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

Quick: How long is this string?

Riddle me this: how long is the following string:

to_char (10, '0999')

Business Rule: Only One per Day, but keep the time

The business rule states:

Only one entry is allowed per ID and per day and the time should be recorded.

The table involved (simplified for the blog post)

SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  );

Wouldn't it be nice if it was possible to do it like this?

SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  ,constraint one_per_day unique (id, trunc (inspection_dt))

  5  );

,constraint one_per_day unique (id, trunc (inspection_dt))

                                    *

ERROR at line 4:

ORA-00904: : invalid identifier

This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.
As you can tell from the error message, this is not allowed.
Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.

Using DBMS_UTILITY to aggregate strings

In my presentation "Analytic Functions Revisited" there is a section on how to string together some columns, string aggregation. Why? There is a new function in Oracle 11g Release 2, called LISTAGG which allows you to do this. This LISTAGG function also has an Analytic counterpart and that's the reason it is in the presentation.
At the UKOUG TEBS conference I did this presentation and someone approached me after wards to talk about these techniques. It boiled down to "Why do it the hard way when there is a built in package that allows you to do this for you?"
The built in package he was referring to is, note the title of this blog entry, DBMS_UTILITY. There are some downsides of using DBMS_UTILITY, more than I initially thought.

The Case for the Case Statement

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.

Analytic Function: Finding Gaps

A little while ago Anton Nielsen posted a blog named "SQL for Spanned Data".

In this blog he describes a challenging query involving a table which stores start and end dates. The challenge as stated by Anton:

The challenge was to create a sql statement to only return contiguous spans by division.

To illustrate what is required:

Id, Division, Start_date, End_date
10 1 09-JUN-2009 10-JUN-2009
11 1 11-JUN-2009 12-JUN-2009
12 1 13-JUN-2009 14-JUN-2009 -- Note the following row is not contiguous
14 1 17-JUN-2009 18-JUN-2009
15 1 19-JUN-2009 20-JUN-2009

The final result will be

Division, Start_date, End_date
1 09-JUN-2009 14-JUN-2009
1 17-JUN-2009 20-JUN-2009

In this post, I will use Analytic Functions to find contiguous spans. As Anton already provided the DDL for this blog, I will not be repeating that here. So, if you want to follow along grab the DDL from his blog and join the fun.

The logic that we are using, is like this

1. compare the current start date with the previous end date


2. if the difference between these dates is one day, we have a contiguous span


3. if the difference is anything else (greater than one day or NULL) then we start a new group of spans


4. Now that we have a list of numbers (1 and 0) we do a running total, each span group will have it's own number


5. Get the first and last day of each span group

To compare the current start date with the previous end date, we will use the LAG function. With the LAG function you can "look back" in your result set.
Let's have a look at the effect of this function

SQL> select id
  2       , start_date
  3       , end_date
  4       , lag (end_date) over (partition by division
  5                                  order by start_date
  6                             )
  7    from spantest
  8   where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
  9                        and to_date ('20-06-2009', 'dd-mm-yyyy')
 10   and division = 1
 11   order by start_date
 12  /

        ID START_DAT END_DATE  LAG(END_D
---------- --------- --------- ---------
     73383 02-JUN-09 03-JUN-09
     73384 04-JUN-09 05-JUN-09 03-JUN-09
     73385 06-JUN-09 07-JUN-09 05-JUN-09
     73386 08-JUN-09 09-JUN-09 07-JUN-09
     73387 10-JUN-09 11-JUN-09 09-JUN-09
     73388 12-JUN-09 13-JUN-09 11-JUN-09
     73389 14-JUN-09 15-JUN-09 13-JUN-09
     73391 18-JUN-09 19-JUN-09 15-JUN-09
     73392 20-JUN-09 21-JUN-09 19-JUN-09

9 rows selected.

The last column is the end date of the previous record. This makes it easy to implement steps 2 and 3. For this we will use a CASE statement

SQL> select id
  2       , start_date
  3       , end_date
  4       , case
  5          when start_date - 
  6             lag (end_date) over (partition by division
  7                                      order by start_date
  8                                 ) = 1
  9          then 0
 10          else 1
 11         end span_group
 12    from spantest
 13   where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 14                        and to_date ('20-06-2009', 'dd-mm-yyyy')
 15   and division = 1
 16   order by start_date
 17  /

        ID START_DAT END_DATE  SPAN_GROUP
---------- --------- --------- ----------
     73383 02-JUN-09 03-JUN-09          1
     73384 04-JUN-09 05-JUN-09          0
     73385 06-JUN-09 07-JUN-09          0
     73386 08-JUN-09 09-JUN-09          0
     73387 10-JUN-09 11-JUN-09          0
     73388 12-JUN-09 13-JUN-09          0
     73389 14-JUN-09 15-JUN-09          0
     73391 18-JUN-09 19-JUN-09          1
     73392 20-JUN-09 21-JUN-09          0

9 rows selected.

The last column (named Span_Group) now contains a list of 1 and 0. The "1" indicate the start of a new Span Group. This sample set therefore contains two Span Groups.
Using the Running Total technique, we can more clearly identify the Span Groups. Because it is not possible to nest analytic functions, we push the query we build so far into an inline view. Then we can use the SUM () OVER () on the Span Groups we created earlier.

SQL> select id
  2       , start_date
  3       , end_date
  4       , sum (span_group) over (partition by division
  5                                    order by start_date
  6                               ) span_grps
  7    from (
  8        select id
  9           , division
 10           , start_date
 11           , end_date
 12           , case
 13              when start_date - 
 14                 lag (end_date) over (partition by division
 15                                            order by start_date
 16                                      ) = 1
 17              then 0
 18              else 1
 19              end span_group
 20        from spantest
 21        where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 22                             and to_date ('20-06-2009', 'dd-mm-yyyy')
 23        and division = 1
 24        )
 25   order by start_date
 26  /

        ID START_DAT END_DATE   SPAN_GRPS
---------- --------- --------- ----------
     73383 02-JUN-09 03-JUN-09          1
     73384 04-JUN-09 05-JUN-09          1
     73385 06-JUN-09 07-JUN-09          1
     73386 08-JUN-09 09-JUN-09          1
     73387 10-JUN-09 11-JUN-09          1
     73388 12-JUN-09 13-JUN-09          1
     73389 14-JUN-09 15-JUN-09          1
     73391 18-JUN-09 19-JUN-09          2
     73392 20-JUN-09 21-JUN-09          2

9 rows selected.

The last column in the result set (named SPAN_GRPS) now clearly identifies the two groups.

The final thing we need to do is retrieve the earliest start date and the latest end date, a simple aggregate will suffice

SQL> select division
  2       , min (start_date) start_date
  3       , max (end_date)   end_date
  4    from (
  5     select id
  6        , division
  7        , start_date
  8        , end_date
  9        , sum (span_group) over (partition by division
 10                                      order by start_date
 11                                ) span_grps
 12     from (
 13           select id
 14              , division
 15              , start_date
 16              , end_date
 17              , case
 18                 when start_date - 
 19                    lag (end_date) over (partition by division
 20                                               order by start_date
 21                                         ) = 1
 22                 then 0
 23                 else 1
 24                 end span_group
 25           from spantest
 26           where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 27                                and to_date ('20-06-2009', 'dd-mm-yyyy')
 28           and division = 1
 29           )
 30     )
 31  group by division, span_grps
 32  /

  DIVISION START_DAT END_DATE
---------- --------- ---------
         1 02-JUN-09 15-JUN-09
         1 18-JUN-09 21-JUN-09

Personally I think that Analytic Functions are a lot easier to understand than the CONNECT BY query. Just out of curiosity I ran both Queries with Autotrace on, and here are the Statistics on the queries:

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
        248  consistent gets
          0  physical reads
          0  redo size
     112543  bytes sent via SQL*Net to client
       3164  bytes received via SQL*Net from client
        255  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3800  rows processed

The CONNECT BY Query showed these statistics:

Statistics
---------------------------------------------------
          3  recursive calls
        233  db block gets
       3882  consistent gets
        224  physical reads
        692  redo size
     112543  bytes sent via SQL*Net to client
       3164  bytes received via SQL*Net from client
        255  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
       3800  rows processed

And yes, I did run the latter query a few times to reduce the recursive calls (the first time around 161 recursive calls)

Enough said,.. Another case to show the power of Analytic Functions.
Final Note:
The results which I got from my query are the same as the results from the first query in the original post.
The query, which Anton labeled "Don't run this"... well, I did run it and got these results for the Division 1 in June:

1 02-JUN-09 15-JUN-09
1 04-JUN-09 15-JUN-09
1 06-JUN-09 15-JUN-09
1 08-JUN-09 15-JUN-09
1 10-JUN-09 15-JUN-09
1 12-JUN-09 15-JUN-09
1 14-JUN-09 15-JUN-09

And I must admit, I don't understand these results... They are completely different than my query and Anton's first query. Maybe I don't understand the original requirements,...