Thanks to Alex Nuijten
http://nuijten.blogspot.ae/2015/04/refresh-multiple-materialized-views-in.html
To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn’t find it).
The procedure that I initially wrote was the following:
1 2 3 4 5 6 7 8 9 10 11 | create or replaceprocedure refresh_mviewsis l_mviews dbms_utility.uncl_array;begin l_mviews(1) := 'ABC_MV'; l_mviews(2) := 'DEF_MV'; l_mviews(3) := 'GHI_MV'; dbms_mview.refresh (tab => l_mviews);end refresh_mviews;/ |
On line 4 a local variable is declared on the type DBMS_UTILITY.UNCL_ARRAY. The declaration of this type is
1 | TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER; |
On lines 6 through 8 the array is filled with the names of the Materialized Views that I want to refresh.
The actual refresh is done on line 9.
When executing the code above, the following exception is raised:
1 2 3 4 5 6 7 8 9 | Error report -ORA-01403: Geen gegevens gevonden.ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 2809ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 3025ORA-06512: in "ALEX.REFRESH_MVIEWS", regel 13ORA-06512: in regel 201403. 00000 - "no data found"*Cause: No data was found from the objects.*Action: There was no data from the objects which may be due to end of fetch. |
Strange…
After some googling I found some old documentation (from Oracle 9i) describing the functionality of the REFRESH procedure in the DBMS_MVIEW pacakge:
If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL.
This explains the exception that is being raised.
Adding line 9 in the code below fixes this problem:
1 2 3 4 5 6 7 8 9 10 11 12 | create or replaceprocedure refresh_mviewsis l_mviews dbms_utility.uncl_array;begin l_mviews(1) := 'ABC_MV'; l_mviews(2) := 'DEF_MV'; l_mviews(3) := 'GHI_MV'; l_mviews(4) := null; dbms_mview.refresh (tab => l_mviews);end refresh_mviews;/ |