Thanks to Alex Nuijten
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:
On line 4 a local variable is declared on the type DBMS_UTILITY.UNCL_ARRAY. The declaration of this type is
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:
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: