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).
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
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:
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:
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; /
thank u bro, u save my time
ReplyDelete