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 replace procedure refresh_mviews is 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 2809 ORA-06512: in "SYS.DBMS_SNAPSHOT" , regel 3025 ORA-06512: in "ALEX.REFRESH_MVIEWS" , regel 13 ORA-06512: in regel 2 01403. 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 replace procedure refresh_mviews is 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; / |