24 April 2015

Refresh Multiple Materialized Views in One Go: No Data Found

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:
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;
/

Documentation

  1. Oracle 9i Documentation
  2. Oracle 12c Documentation

1 comment: