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

17 April 2015

APEX 5: forgot the images?

On my play environment I usually use Oracle APEX with the Embedded PL/SQL Gateway, just because it's so easy.
When a new version of APEX is released, just like everybody else, I upgrade my play environment.
After the apexins.sql script is run, I always want to start playing with it immediately. Usually it is at this point where I just see a blank page... scratching my head wondering why it is not running,... having to go back to the documentation to realise I forgot the last step - configure the EPG...
Now with APEX5 an alert is shown when you forget the last step:

Immediately you know what to do... :)
Another one of those little enhancements that makes APEX5 simply awesome.

08 April 2015

Speed Up Development with Logger: Generate a Template

Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
When an exception occurs, it is quite handy to have all the arguments at your disposal to make it easier to debug your code. But to include the arguments manually when you are writing code is quite a lot of work.
You can download (or fork or whatever) the package on GitHub.

How does it work?

Install the package, which contains a single procedure called Template.

   procedure template (p_procedure  in varchar2
                      ,p_standalone in boolean := false
                      );
The Template procedure has two arguments; one for the complete (pacakged) procedure name, and one to indicate if it is standalone (not pacakged) or not.

When you have written the procedure (or function) signature, something like

create or replace package demo_pkg is
   
   procedure test (p_arg1 in     varchar2
                  ,p_arg2 in out varchar2
                  ,p_arg3 out    varchar2
                  );

end demo_pkg;
/
... and when it compiles successfully, then you can generate the stored procedure body using my generator_pkg.
The package uses DBMS_OUTPUT to show the template, so you should set the serveroutput on in SQL*Plus.
set serveroutput on format wrapped

begin
   generator_pkg.template ('demo_pkg.test');
end;
/
This will generate the following:
   is
      l_scope  constant varchar2(61) := g_package||'test';
      l_params logger.tab_param;
   begin
      logger.append_param (p_params => l_params, p_name => 'p_arg1', p_val => p_arg1);
      logger.append_param (p_params => l_params, p_name => 'p_arg2', p_val => p_arg2);
      logger.log_information (p_text => 'Start'
        ,p_scope => l_scope
        ,p_params => l_params
        );
      [==> Actual Program goes here ==]
      logger.log_information (p_text => 'End'
        ,p_scope => l_scope
        );
   end test;
Because it is a packaged procedure the l_scope variable contains a reference to g_package.
Each of my packages contains a Global Constant called g_package which is defined as
   g_package constant varchar2(31) := $$plsql_unit || '.';

Now that the template is generated, simply copy-and-paste it in your editor and start to write the actual program where it says

   [==> Actual Program goes here ==]
And that's it, now you have a starting point for development including the references to Logger.

The blogs that Martin wrote: