Constants and package state revisited

I have previously noted that numeric constants declared at the package level don’t create package state, and that strings and (weirdly) negative numbers do create package state. Happily, under Oracle 19c and above, no package level constants create package state.

We can test this using a very basic package (this test done using XE 21c):

CREATE OR REPLACE PACKAGE pkg_constants
AS

   FUNCTION get_string
   RETURN VARCHAR2;

   FUNCTION get_number
   RETURN NUMBER;

   FUNCTION get_negative_number
   RETURN NUMBER;

END pkg_constants;
/

CREATE OR REPLACE PACKAGE BODY pkg_constants
AS

   c_string            CONSTANT VARCHAR2(20) := 'abc';
   c_number            CONSTANT NUMBER := 100;
   c_negative_number   CONSTANT NUMBER := -100;

   FUNCTION get_string
   RETURN VARCHAR2
   AS
   BEGIN
      RETURN c_string;
   END get_string;

   FUNCTION get_number
   RETURN NUMBER
   AS
   BEGIN
      RETURN c_number;
   END get_number;

   FUNCTION get_negative_number
   RETURN NUMBER
   AS
   BEGIN
      RETURN c_negative_number;
   END get_negative_number;

END pkg_constants;
/

Using a simple query:

select pkg_constants.get_string as string_value
,      pkg_constants.get_number as number_value
,      pkg_constants.get_negative_number as negative_number
from   dual;

we can list the package level values:

STRING_VALUE    NUMBER_VALUE NEGATIVE_NUMBER
--------------- ------------ ---------------
abc                      100            -100

In another session if we now recompile the package body with new values for the constants:

CREATE OR REPLACE PACKAGE BODY pkg_constants
AS

   c_string       CONSTANT VARCHAR2(20) := 'xyz';
   c_number       CONSTANT NUMBER := 99;
   c_neg_number   CONSTANT NUMBER := -99;

   FUNCTION get_string
   RETURN VARCHAR2
   AS
   BEGIN
      RETURN c_string;
   END get_string;

   FUNCTION get_number
   RETURN NUMBER
   AS
   BEGIN
      RETURN c_number;
   END get_number;

   FUNCTION get_negative_number
   RETURN NUMBER
   AS
   BEGIN
      RETURN c_negative_number;
   END get_negative_number;

END pkg_constants;
/

When we rerun the query in our previous session, we get the new values and not ORA-04068:

select pkg_constants.get_string as string_value
,      pkg_constants.get_number as number_value
,      pkg_constants.get-negative_number as negative_number
from   dual;

STRING_VALUE    NUMBER_VALUE NEGATIVE_NUMBER<
--------------- ------------ ---------------
xyz                       99             -99

If we remove the CONSTANT keywords in the package body, i.e. turn the constants into package level variables, then rerunning the query after the second compilation of the package body results in the familiar ORA-04068.

Mutating table error (ORA-04091) but no trigger?!?

I encountered an interesting scenario recently… a “mutating table” error but the reported table had no trigger on it.

The Oracle documentation has this to say about ORA-04091:

ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

Basically, if we have a row level trigger on a table then our trigger cannot read or write the same table that the trigger belongs to. The following illustrates this:

SQL> create table src_tab
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,constraint src_tab_pk primary key (id));

Table created.

SQL> insert /*+ append */ into src_tab
  2    (id, val)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  from   row_gen r1
  7  ,      row_gen r2;

1000000 rows created.

SQL> commit;

SQL> create or replace trigger src_tab_aiu
  2  after insert or update
  3  on src_tab
  4  for each row
  5  declare
  6    l_temp number;
  7  begin
  8  
  9    select count(*)
 10    into   l_temp
 11    from   src_tab;
 12  
 13  end src_tab_aiu;
 14  /

Trigger created.

SQL> update src_tab
  2  set val = 'y'
  3  where id = 1234;
update src_tab
       *
ERROR at line 1:
ORA-04091: table DEVELOPER.SRC_TAB is mutating, trigger/function may not see it 
ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 5 
ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU'

In the above we created a table, populated it with a million rows, added a row level trigger that counts the number of rows in the table and then attempted to update a row. The update caused the trigger to fire, which then failed with our ORA-04091 mutating table error.

The scenario I encountered recently reported a valid trigger in the system but the table noted from the error was not the table upon which the trigger was defined. The trigger in question did insert into the reported table and had been operating successfully for a number of years so the usual cause of the ORA-04091 exception was puzzling…

… until one of the DBAs reported that they had been doing some online table maintenance. The cause of the ORA-04091 scenario was then easy to replicate.

We’ll start with a source table and define a trigger that inserts the data changes into a log table, mimicking what might be an audit or change capture process. We’ll then run a series of updates against the source table and at the same time perform an online move of the log table (this was run on a 21c Express Edition database):

SQL> create table src_tab
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,constraint src_tab_pk primary key (id));

Table created.

SQL> insert /*+ append */ into src_tab
  2    (id, val)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  from   row_gen r1
  7  ,      row_gen r2;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create table src_tab_log
  2    (id     number(10) not null
  3    ,val    char(10) not null
  4    ,update_timestamp timestamp not null);

Table created.

SQL> insert /*+ append */ into src_tab_log
  2    (id, val, update_timestamp)
  3  with row_gen as (select rownum as rn from dual connect by level <= 1000)
  4  select rownum
  5  ,      'x'
  6  ,      systimestamp
  7  from   row_gen r1
  8  ,      row_gen r2;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create or replace trigger src_tab_aiu
  2  after insert or update
  3  on src_tab
  4  for each row
  5  declare
  6  begin
  7  
  8    insert into src_tab_log
  9      (id
 10      ,val
 11      ,update_timestamp)
 12    values
 13      (:NEW.id
 14      ,:NEW.val
 15      ,systimestamp);
 16  
 17    dbms_session.sleep (0.01); -- just to slow down the processing
 18  
 19  end src_tab_aiu;
 20  /

Trigger created.

SQL> update src_tab -- to show that the trigger does work
  2  set val = 'y'
  3  where id = 1234;

1 row updated.

SQL> commit;

Commit complete.

SQL> begin -- run lots of updates and in another session run an online move of src_tab_log
  2    for i in 1..1000
  3    loop
  4      update src_tab
  5      set val = 'y'
  6      where id = i*100;
  7      commit;
  8    end loop;
  9  end;
 10  /
begin
*
ERROR at line 1:
ORA-04091: table DEVELOPER.SRC_TAB_LOG is mutating, trigger/function may not 
see it 
ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 4 
ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU' 
ORA-06512: at line 4 

Whilst the last block updating the table rows was running, the following was kicked off in another session:

SQL> alter table src_tab_log move online;

which caused the reported ORA-04091.

Online partitioning of a table has the same impact. It seems versions 19c and 21c are affected.

So, our online table maintenance operation can interfere with the usual operation of the database when the table is subject to DML invoked from a trigger. I’m kinda inclined to call “bug” on this but I guess Oracle might like to describe it as an “unavoidable feature”. Regardless, it’s something to be aware of and to avoid online table reorganisations on tables subject to trigger sourced DML.

PL/SQL Conditional Compilation

I’ve been going through the motions of database upgrades at work. From Oracle 12.2 onwards, Oracle made changes to the call interface for a number of their in-built packages that involve encrypted external communication (utl_tcp, utl_http, utl_smtp, etc). For example, the call to the routine utl_http.begin_request has gone from:

UTL_HTTP.BEGIN_REQUEST (
   url               IN  VARCHAR2,
   method            IN  VARCHAR2 DEFAULT 'GET',
   http_version      IN  VARCHAR2 DEFAULT NULL,
   request_context   IN  request_context_key DEFAULT NULL)
RETURN req;

to:

UTL_HTTP.BEGIN_REQUEST (
   url               IN  VARCHAR2,
   method            IN  VARCHAR2 DEFAULT 'GET',
   http_version      IN  VARCHAR2 DEFAULT NULL,
   request_context   IN  request_context_key DEFAULT NULL,
   https_host        IN  VARCHAR2 DEFAULT NULL)
RETURN req;

When creating an https connection there’s a new parameter that needs to be supplied. The change is quite trivial but how should you handle this change from within your source code when you will have databases of different versions? You are probably faced with the scenario that your development and test databases get upgraded some time before your production databases. Development does not stop whilst this is going on. Furthermore, you don’t really want to remember to deploy new versions of code at the point in time of the upgrade as that’s yet another thing to remember within an already complicated process. Conditional compilation offers us a solution.

Using conditional compilation we can support different code sections at the same time and have Oracle use the one that is appropriate to the database version that is executing the code. For example, I use the above utl_http routines to post to Slack and pre and post 12.2 database versions can be supported using the following:

-- for Oracle 12.1 and earlier
$IF (DBMS_DB_VERSION.VERSION = 12 AND DBMS_DB_VERSION.RELEASE = 1) OR (DBMS_DB_VERSION.VERSION < 11)      
   l_http_request := utl_http.begin_request (utl          => l_slack_webhook_url
                                            ,method       => 'POST'
                                            ,http_version => 'HTTP/1.1');
-- for Oracle 12.2 and above...
$ELSE
   l_http_request := utl_http.begin_request (utl          => l_slack_webhook_url
                                            ,method       => 'POST'
                                            ,http_version => 'HTTP/1.1'
                                            ,https_host   => 'slack.com');
$END

It’s quite easy to see what’s going on in the above code block. There are some lines that commence with $ and these lines are the conditional compilation lines. In this case I have a conditional IF THEN ELSE block that evaluates the database version and will compile the correct version of the utl_http call for that version.

Conditional compilation is another tool for the PL/SQL toolbox that’s quite handy in certain scenarios. The documentation has further suggested uses and the full breadth of functionality available.

Edition-based Redefinition to avoid ORA-04068

I have previously written about how to avoid package state to facilitate on-line deployments, so that PL/SQL code can be modified in a live environment without impacting existing sessions. The main drawback to the methods outlined are that they assume the call interface, or contract, for routines won’t change. When dealing with complex deployments, Oracle 11g Release 2 and later provides for a versioning capabilitiy in the form of edition-based redefinition.

Let’s take a quick look at how edition-based redefinition could be used to avoid ORA-04068. We’ll use the inital package code from the previous article that makes no effort to avoid package state. However, first we need to do a little bit of preparation. From an appropriately authorised account we will set up a new EDITION_USER account and create a couple of editions within the database:

CREATE USER edition_user
   IDENTIFIED BY edition_user;

ALTER USER edition_user ENABLE EDITIONS;
GRANT CONNECT TO edition_user;
GRANT CREATE PROCEDURE TO edition_user;

CREATE EDITION edition_1 AS CHILD OF ora$base;
CREATE EDITION edition_2 AS CHILD OF edition_1;

GRANT USE ON EDITION edition_1 TO edition_user;
GRANT USE ON EDITION edition_2 TO edition_user;

We’ve now got a new account and two new editions that the account has access to. The above assumes no previous editions have been defined, i.e. the default ORA$BASE edition was still in effect.

From a new session logged onto the new EDITION_USER account we’ll create our package under EDITION_1:

ALTER SESSION SET EDITION = edition_1;

CREATE OR REPLACE EDITIONABLE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable;
   END get_variable;

END pkg_state;
/

Running the packaged routines within the session we get:

SQL> EXEC pkg_state.set_variable (5)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

Everything working as expected. Our session has now got package state for PKG_STATE. In a new session we’re going to modify the package, doubling the value we output in the GET_VARIABLE routine, but we’ll do it under EDITION_2:

ALTER SESSION SET EDITION = edition_2;

CREATE OR REPLACE EDITIONABLE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable * 2;
   END get_variable;

END pkg_state;
/

Executing the code from within the EDITION_2 session we see that all is working as it should:

SQL> EXEC pkg_state.set_variable (5)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 10

PL/SQL procedure successfully completed.

Let’s go back to our initial session and see what happens if we execute the GET_VARIABLE packaged routine again:

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

We can see that it’s still executing the initial version of the code and that it’s still got its package state. The compilation of the code under EDITION_2 did not impact our first session, which is running under EDITION_1. If we change out first session to EDITION_2 and run the routines everything works fine too:

SQL> ALTER SESSION SET EDITION = edition_2;

Session altered.

SQL> EXEC pkg_state.set_variable (2)

PL/SQL procedure successfully completed.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 4

PL/SQL procedure successfully completed.

We can see that now we’re executing the modified code under EDITION_2. Switching back to EDITION_1 we find that our package state from the EDITION_1 execution has been preserved:

SQL> ALTER SESSION SET EDITION = edition_1;

Session altered.

SQL> EXEC dbms_output.put_line ('get_value: ' || TO_CHAR(pkg_state.get_variable));
get_value: 5

PL/SQL procedure successfully completed.

So, edition-based redefinition allows us to have multiple versions of our package code in the database at the same time. Each version has its own package state so compiling a new version of the code into a different edition does not invalidate the package state associated with sessions using a different edition.

A word of caution however, Edition-based redefinition is a very powerful feature and one that needs careful planning before using it. I would strongly recommend reading the on-line documentation before implementating anything. Editions are database-wide and currently, as of Oracle 12.1.0.2, limited to a simple single parent-child sequence. For manageability reasons, care should be taken not to create too many editions with various changes in each edition. If at all possible, I would recommend there be just 2 active editions: a “current” one and a “future” one. Once the functionality of the future edition has been confirmed and all applications have been moved over to be using it then that becomes the current edition and the previous current edition is removed. Trying to track a dozen or more active editions, all with their own specific changes, is likely to lead to trouble.

AQ Subscriber Rules

So far in event-based processing posts using AQ we’ve looked at callback routines and multi-subscriber queues. Next we’ll look into another useful AQ feature; rule-based subscriptions.

In the last post, the routine to subscribe to the queue, inside emp_event_management.add_subscriber, was:

dbms_aqadm.add_subscriber (queue_name => c_queue_owner || '.' || c_queue_name
                          ,subscriber => SYS.AQ$_AGENT(p_subscriber,NULL,NULL));

where:

• c_queue_owner is the owner of the queue table
• c_queue_name is the name of the queue
• p_subscriber is the subscriber name being added

The ADD_SUBSCRIBER routine is able to accept another parameter, named RULE, which contains a Boolean expression that is evaluated when a message is queued. Here’s what the Oracle 12.1 documentation says about the rule parameter:

A conditional expression based on the message properties, the message data properties and PL/SQL functions. A rule is specified as a Boolean expression using syntax similar to the WHERE clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the where clause of a SQL query). Currently supported message properties are priority and corrid.

To specify rules on a message payload (object payload), use attributes of the object type in clauses. You must prefix each attribute with tab.user_data as a qualifier to indicate the specific column of the queue table that stores the payload. The rule parameter cannot exceed 4000 characters.

So, for our employee events example code, suppose we needed to send a message to the people involved in relocating a staff member when they changed department. We could set up a subscriber to recieve all messages and discard those messages that are not “change department” messages but it would be more efficient to set the subscriber to only receive the “change department” messages in the first place. To do so we would need to construct the following rule:

tab.user_data.event_operation = 'CHANGE_DEPT'

In the above expression:

• tab.user_data is a reference to the message payload object
• event_operation is an attribute of our queue payload type (see the t_emp_event definition)
• the ‘CHANGE_DEPT’ string is event_operation value for a Change Department message (see constants within the EMP_EVENT_MANAGEMENT package spec)

While the rule string can be quite flexible, for our employee event code example I’m going to assume that we’ll restrict subscribers to simply choosing the employee events they wish to received. As a consequence, the ADD_SUBSCRIBER routine in the package EMP_EVENT_MANAGEMENT will be extended to accept a collection of strings, where each string would be an employee event (i.e. the event_operation of the t_emp_event type).

The first change is to create a collection type. I’ve gone with a schema level nested table type over an associative array as a nested table may be intrinsically NULL, allowing a cleaner interface that is backwards compatible with the previous version of the EMP_EVENT_MANAGEMENT code:

CREATE TYPE t_emp_event_type_tab
   IS TABLE OF VARCHAR2(20)

Next we add a new parameter to the ADD_SUBSCRIBER routine in the EMP_EVENT_MANAGEMENT package spec:

PROCEDURE add_subscriber (p_subscriber         IN VARCHAR2
                         ,p_error_on_duplicate IN BOOLEAN DEFAULT TRUE
                         ,p_emp_event_types    IN t_emp_event_type_tab DEFAULT NULL);

Lastly we add the changes to the ADD_SUBSCRIBER routine in the package body:

PROCEDURE add_subscriber (p_subscriber         IN VARCHAR2
                         ,p_error_on_duplicate IN BOOLEAN        DEFAULT TRUE
                         ,p_emp_event_types    IN t_emp_event_type_tab DEFAULT NULL)
IS
   ex_duplicate_subscriber EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_duplicate_subscriber,-24034);
   
   l_subscription_rule  VARCHAR2(1000);
BEGIN

   -- construct a rule string if we've been given one or more emp event strings
   IF (p_emp_event_types IS NOT NULL AND p_emp_event_types.COUNT > 0) THEN
      -- the initial part of the rule string
      l_subscription_rule := 'tab.user_data.event_operation IN (';
      -- append a comma separate list of emp event types strings
      FOR i IN 1..p_emp_event_types.COUNT
      LOOP
         l_subscription_rule := l_subscription_rule || '''' || p_emp_event_types(i) || ''',';
      END LOOP;
      -- replace the trailing comma with a closing bracket to complete the rule
      l_subscription_rule := SUBSTR(l_subscription_rule,1,LENGTH(l_subscription_rule)-1) || ')';
   END IF;
   
   dbms_aqadm.add_subscriber (queue_name => c_queue_owner || '.' || c_queue_name
                             ,subscriber => SYS.AQ$_AGENT(p_subscriber,NULL,NULL)
                             -- add the constructed rule string as a parameter when
                             -- add a subscriber to the
                             ,rule       => l_subscription_rule);
   msg ('Added ' || p_subscriber || ' subscriber to queue ' || c_queue_owner || '.' || c_queue_name);
   msg ('Subscription rule was : ' || NVL(l_subscription_rule,'<>'));
EXCEPTION
   WHEN ex_duplicate_subscriber THEN
      IF p_error_on_duplicate THEN
         RAISE;
      END IF;
END add_subscriber;

Using the test script we can add a subscriber that is interested only in the CHANGE_DEPT evnts, enqueue different event type messages and see which ones it ends up processing:

DECLARE
   l_emp_event_type_tab t_emp_event_type_tab := t_emp_event_type_tab('CHANGE_DEPT');
BEGIN
   emp_event_management.add_subscriber (p_subscriber      => 'TEST_SCRIPT'
                                       ,p_emp_event_types => l_emp_event_type_tab);
END;
/

EXEC emp_event_processing.update_sal (p_empno=>7654,p_sal=>1600)
EXEC emp_event_processing.update_dept (p_empno=>7654,p_deptno=>20)
COMMIT;

DECLARE
   ex_queue_timeout     EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_queue_timeout, -25228);
   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     RAW(16);
   l_payload            t_emp_event;
   l_subscriber_name    VARCHAR2(30) := 'TEST_SCRIPT';
BEGIN

   l_dequeue_options.wait          := 5; -- some nominal wait for a message to arrive
   l_dequeue_options.consumer_name := l_subscriber_name;

   -- loop through the messages until we find no more
   LOOP
      BEGIN
         dbms_aq.dequeue (queue_name         => emp_event_management.c_queue_owner || '.' || emp_event_management.c_queue_name
                         ,dequeue_options    => l_dequeue_options
                         ,message_properties => l_message_properties
                         ,payload            => l_payload
                         ,msgid              => l_message_handle);

         -- here's where we would normallydo our message processing, which is
         -- just to dump some details of the message
         dbms_output.put_line ('Processed ' || l_payload.event_operation || ' event for emp ' || TO_CHAR(l_payload.empno));
      EXCEPTION
         WHEN ex_queue_timeout THEN
            EXIT;
      END;
   END LOOP;

END;
/

The output of which is:

Processed CHANGE_DEPT event for emp 7654

PL/SQL procedure successfully completed.

indicating that while we queued a change department and a change salary event, our test script only processed the former, which is exactly what our subscription rule was supposed to do.

---

The full set of scripts for the above are available for download.

AQ Multiple Subscribers

In the last post we looked at asynchronous processing using a queue and a callback routine. Event-based processing is very powerful and flexible. Oracle’s Advanced Queues provide many features that are useful in setting up event-based processing. This post will look at muti-subscriber queues.

The queue created for the previous post was a single subscriber queue, i.e. a message is available for being dequeued just once so it’s pretty much limited to a single dequeue process. Queues can also be configured to support multiple subscribers to the queue, allowing us to initiate multiple business processes from the one event. In this scenario, each subscriber is able to dequeue the same message.

The changes to our previous code to support multi-subscriber queue are relatively few. When creating the queue table we need to specify that it will support multiple subscribers. In the routine SETUP inside the package EMP_EVENT_MANAGEMENT you will find the following change:

dbms_aqadm.create_queue_table (queue_table        => c_queue_owner || '.' || c_queue_table
                              ,queue_payload_type => c_queue_payload
                              ,multiple_consumers => TRUE);

We also have routines to add and remove queue subscribers exposed in the package spec:

PROCEDURE add_subscriber (p_subscriber         IN VARCHAR2
                         ,p_error_on_duplicate IN BOOLEAN DEFAULT TRUE);

PROCEDURE remove_subscriber (p_subscriber         IN VARCHAR2
                            ,p_error_on_not_exist IN BOOLEAN DEFAULT TRUE);

The package body has an additional routine for removing all subscribers from the queue, which invoked by the teardown routine to clean things up properly.

When creating a process that will consume messages from a queue, the process will need to:

• add itself as a subscriber of the queue
• specify its subscriber name when dequeuing messages

When registering a callback routine, the queue subscriber name is specified after the name of the callback routine, as shown below:

PROCEDURE register_event_callback
IS
BEGIN
   dbms_aq.register
      (sys.aq$_reg_info_list
         (sys.aq$_reg_info
             -- the next bit needs to be "queue_name : subscriber"
            (c_queue_owner || '.' || c_queue_name || ':' || c_callback_subscriber
            ,DBMS_AQ.NAMESPACE_AQ
             -- this is the routine that will get called when a message is queued
             -- I'm going to assume it's in the same schema as the queue
            ,'plsql://' || c_queue_owner || '.emp_event_processing.emp_event_callback'
            ,NULL
            )
         ),
      1
      );
   msg('Callback registered');
END register_event_callback;

The unregister routine is similarly modified to include the subscriber.

Looking at the callback routine itself, contained in the EMP_EVENT_PROCESSING package, we need to specify the subscriber when dequeuing the message although this is provided in the details passed into the callback routine:

PROCEDURE emp_event_callback (context    RAW
                             ,reginfo    SYS.AQ$_REG_INFO
                             ,descr      SYS.AQ$_DESCRIPTOR
                             ,payload    RAW
                             ,payloadl   NUMBER)
IS
   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     RAW(16);
   l_payload            t_emp_event;
BEGIN

   msg ('Entering emp_event_callback');
   -- dequeue the message specified
   l_dequeue_options.msgid         := descr.msg_id;
   l_dequeue_options.wait          := dbms_aq.no_wait;
   l_dequeue_options.consumer_name := descr.consumer_name;

   dbms_aq.dequeue (queue_name         => descr.queue_name
                   ,dequeue_options    => l_dequeue_options
                   ,message_properties => l_message_properties
                   ,payload            => l_payload
                   ,msgid              => l_message_handle);

   process_message (p_emp_event => l_payload);

   msg ('Processed ' || l_payload.event_operation || ' event for emp ' || TO_CHAR(l_payload.empno));

   -- commit here to ensure we remove the message
   COMMIT;

END emp_event_callback;

All of these changes are required simply to get the previous code working on a multi-subscriber queue. We still don’t have a second subscriber. We could set up a second callback routine or simply test our queue using a test script, like the one following:

EXEC emp_event_management.add_subscriber (p_subscriber => 'TEST_SCRIPT')

EXEC emp_event_processing.update_sal (p_empno=>7654,p_sal=>1600)
EXEC emp_event_processing.update_dept (p_empno=>7654,p_deptno=>20)
COMMIT;

DECLARE
   ex_queue_timeout     EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_queue_timeout, -25228);
   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     RAW(16);
   l_payload            t_emp_event;
   l_subscriber_name    VARCHAR2(30) := 'TEST_SCRIPT';
BEGIN

   l_dequeue_options.wait          := 5; -- some nominal wait for a message to arrive
   l_dequeue_options.consumer_name := l_subscriber_name;

   -- loop through the messages until we find no more
   LOOP
      BEGIN
         dbms_aq.dequeue (queue_name         => emp_event_management.c_queue_owner || '.' || emp_event_management.c_queue_name
                         ,dequeue_options    => l_dequeue_options
                         ,message_properties => l_message_properties
                         ,payload            => l_payload
                         ,msgid              => l_message_handle);

         -- here's where we would normallydo our message processing, which is
         -- just to dump some details of the message
         dbms_output.put_line ('Processed ' || l_payload.event_operation || ' event for emp ' || TO_CHAR(l_payload.empno));
      EXCEPTION
         WHEN ex_queue_timeout THEN
            EXIT;
      END;
   END LOOP;

END;
/

-- commit here to ensure we remove the message
COMMIT;

The output from the mainpart of the above script is:

Processed UPDATE_SAL event for emp 7654
Processed CHANGE_DEPT event for emp 7654

and if we take a look at the MSG_LOG table where our callback routine logs its processing activity:

SQL> SELECT *
  2  FROM   msg_log
  3  WHERE  msg_timestamp >= SYSTIMESTAMP - INTERVAL '2' MINUTE
  4  ORDER  BY
  5         msg_id DESC
  6  /

MSG_ID MSG_TIMESTAMP   MSG
------ --------------- --------------------------------------------------
   290 06:44:31.481000 Processed CHANGE_DEPT event for emp 7654
   289 06:44:31.481000 Entering emp_event_callback
   288 06:44:31.481000 Processed UPDATE_SAL event for emp 7654
   287 06:44:31.481000 Entering emp_event_callback

we can see that it too has processed the messages.

---

The full set of scripts for the above are available for download.

Asynchronous processing using AQ Callback

Database transactions are awesome! The ability to do multiple changes to data in various tables and to commit those changes so that they all appear to other sessions at once, or to roll them all back as if nothing had happened is one of the most powerful features of relational databases.

… and here’s the “but”; but sometimes you don’t want to do all processing in one transaction. For example, with the on-line systems of today we might want to do the minimum processing needed to fulfil an order in a single transaction and defer all non-immediate processing to maximise throughput. “Near real time” would be the way to describe our deferred processing. There are various ways to do this but one way is via queues with callback routines.

So, how do we go about doing this? Here’s a high level summary of the steps involved:

• Create an object type with the details that the processing will require
• Create a queue whose payload is the object type
• Create a callback routine that will be run whenever an item is queued
• Associate the callback routine with the queue so that Oracle knows to call it

Getting into some solid detail we’ll use the EMP table from the SCOTT schema. We’ll assume our business processing has two functions that need to be done asynchronously; update an employees salary and transfer an employee to a different division (okay, probably not great scenarios for asynchronous processing but I wanted something simple using the EMP table…)

Since we’ll be working within the SCOTT schema we need to ensure SCOTT can do Advance Queue operations so we grant SCOTT the ability execute on the packages we will use:

GRANT EXECUTE ON dbms_aqadm TO scott;
GRANT EXECUTE ON dbms_aq    TO scott;

Next we will need to create our object type that the queue will hold. For this example, the type will hold the necessary EMP attributes we need to know about and also an attribute that will store a description of the operation to perform:

CREATE OR REPLACE TYPE t_emp_event
AS OBJECT
  (event_operation VARCHAR2 (20)
  ,empno           NUMBER (4)
  ,sal             NUMBER (7,2)
  ,mgr             NUMBER (4)
  ,deptno          NUMBER (2)
  )
/

Next up, we create our queue on this object type:

BEGIN
   dbms_aqadm.create_queue_table (queue_table        => 'SCOTT.EMP_EVENT_QT'
                                 ,queue_payload_type => 'T_EMP_EVENT'
                                 ,multiple_consumers => FALSE);

   dbms_aqadm.create_queue (queue_name  => 'SCOTT.EMP_EVENT_Q'
                           ,queue_table => 'SCOTT.EMP_EVENT_QT);

   dbms_aqadm.start_queue (queue_name => 'SCOTT.EMP_EVENT_Q');
END;
/

We can now enqueue messages onto our queue with a routine like the following:

PROCEDURE enqueue (p_msg  IN t_emp_event) 
IS
   l_enqueue_options    dbms_aq.enqueue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_msgid              RAW(16);
BEGIN
   dbms_aq.enqueue (queue_name         => 'SCOTT.EMP_EVENT_Q'
                   ,enqueue_options    => l_enqueue_options
                   ,message_properties => l_message_properties
                   ,payload            => p_msg
                   ,msgid              => l_msgid);
END enqueue;

Before we can process our messages we need to set up our callback routine, which has a specific call interface. You can read about what this interface needs to look like in the Oracle documentation:

PROCEDURE emp_event_callback (context    RAW
                             ,reginfo    SYS.AQ$_REG_INFO
                             ,descr      SYS.AQ$_DESCRIPTOR
                             ,payload    RAW
                             ,payloadl   NUMBER)
IS
   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     RAW(16);
   l_payload            t_emp_event;
BEGIN
   l_dequeue_options.msgid         := descr.msg_id;
   l_dequeue_options.wait          := dbms_aq.no_wait;

   dbms_aq.dequeue (queue_name         => descr.queue_name
                   ,dequeue_options    => l_dequeue_options
                   ,message_properties => l_message_properties
                   ,payload            => l_payload
                   ,msgid              => l_message_handle);

   -- here's what we'll do with the message... in this case call
   -- out to a separate processing routine (not shown here)
   process_message (p_emp_event => l_payload);

END emp_event_callback;

The routine above assumes the processing of the message will be done by some other routine called “process_message”. In a full blown solution (see later on) this routine would be part of a package that captures all the logic associated with the EMP event processing.

Now that the callback routine has been defined we register it so that Oracle will call it whenever something is placed in our queue. To do this we need to call the DBMS_AQ.REGISTER routine, telling it the queue and the processing routine it needs to call:

BEGIN
   dbms_aq.register
      (sys.aq$_reg_info_list
         (sys.aq$_reg_info
            ('SCOTT.EMP_EVENT_Q' -- the queue 
            ,DBMS_AQ.NAMESPACE_AQ
            ,'plsql://SCOTT.EMP_EVENT_CALLBACK' -- this is the routine that will get called when a message is queued
            ,NULL)
         ),
      1
      );
END;
/

… and that’s pretty much all there is to it. Keep in mind that the messages on the queue are transactional so they will not be processed until the session that enqueues the messages commits, but this is exactly the behaviour that we would normally desire.

Of course it is much simpler to encapsulate all of the above inside packaged routines. For the above EMP event scenario I have constructed two packages, available for download:

• emp_event_management, which handles the setting up of the queue and registering the callback routine
• emp_event_processing, which contains the routines to queue up events and processing those same event messages

Once all the complicated DBMS_AQ calls are hidden behind a management API, starting up the event processing is just:

EXEC emp_event_management.setup
EXEC emp_event_management.startup

Shutting things down is:

EXEC emp_event_management.shutdown
EXEC emp_event_management.teardown

The package routines write processing messages to a table named MSG_LOG. I’ve gone a bit overboard with the message written out to help understand what’s going on. The messages are timestamped so the speed of processing can be monitored.

Creating events for processing are matter of making calls to our processing package, similar to these:

EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)
EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)

By way of demonstration, let’s execute the above commands against the standard SCOTT.EMP table. We’ll grab the current time so as to filter our MSG_LOG table later on, view the data we’re about to change, queue the messages, note the current time so we can see how long the callback processing takes, list the MSG_LOG entries and lastly view the data again to confirm the changes:

VARIABLE now  VARCHAR2(30)
EXEC :now := TO_CHAR(SYSTIMESTAMP,'dd/mm/yyyy hh24:mi:ss.ff')

SELECT empno
,      ename
,      sal
,      deptno
,      mgr
FROM   emp
WHERE  empno = 7654
/

EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)
EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)
SELECT TO_CHAR(SYSTIMESTAMP,'hh24:mi:ss.ff') AS current_time FROM dual;
COMMIT;

SELECT *
FROM   msg_log
WHERE  msg_timestamp >= TO_TIMESTAMP(:now,'dd/mm/yyyy hh24:mi:ss.ff')
ORDER  BY
       msg_id DESC
/

SELECT empno
,      ename
,      sal
,      deptno
,      mgr
FROM   emp
WHERE  empno = 7654
/

The output of which is:

SQL> VARIABLE now  VARCHAR2(30)
SQL> EXEC :now := TO_CHAR(SYSTIMESTAMP,'dd/mm/yyyy hh24:mi:ss.ff')

PL/SQL procedure successfully completed.

SQL> SELECT empno
  2  ,      ename
  3  ,      sal
  4  ,      deptno
  5  ,      mgr
  6  FROM   emp
  7  WHERE  empno = 7654
  8  /

     EMPNO ENAME             SAL     DEPTNO        MGR
---------- ---------- ---------- ---------- ----------
      7654 MARTIN           1250         30       7698

SQL> EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)

PL/SQL procedure successfully completed.

SQL> EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)

PL/SQL procedure successfully completed.

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'hh24:mi:ss.ff') AS current_time FROM dual;

CURRENT_TIME
------------------
07:09:02.313000

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT *
  2  FROM   msg_log
  3  WHERE  msg_timestamp >= TO_TIMESTAMP(:now,'dd/mm/yyyy hh24:mi:ss.ff')
  4  ORDER  BY
  5         msg_id DESC
  6  /

MSG_ID MSG_TIMESTAMP   MSG
------ --------------- --------------------------------------------------
   237 07:09:02.329000 Processed CHANGE_DEPT event for emp 7654
   236 07:09:02.329000 Entering emp_event_callback
   235 07:09:02.329000 Processed UPDATE_SAL event for emp 7654
   234 07:09:02.329000 Entering emp_event_callback

SQL> SELECT empno
  2  ,      ename
  3  ,      sal
  4  ,      deptno
  5  ,      mgr
  6  FROM   emp
  7  WHERE  empno = 7654
  8  /

     EMPNO ENAME             SAL     DEPTNO        MGR
---------- ---------- ---------- ---------- ----------
      7654 MARTIN           1600         20       7566

All went according to plan. The changes took just a fraction of a second to process from when the messages were queued, which certainly satisfies the criteria of “near real time” to me.

---

The full set of scripts for the above are available for download.

DML within DML

As noted in my previous post, a recent code review revealed a couple of things that I wasn’t aware of… which is one of the pleasures of working in IT; learning new things. Aggregation within the RETURNING INTO clause of a DML statement was a good thing to find out about. I’m having a hard time thinking of a scenario that would legitimately justify the other feature; performing DML within DML…

To demonstrate, let’s create two simple tables, TAB_A and TAB_B, each with two columns; ID and VAL. Both table start off with a small set of data:

CREATE TABLE tab_a
   (id    NUMBER (6) NOT NULL
   ,val   NUMBER (6) NOT NULL
   ,CONSTRAINT tab_a_pk PRIMARY KEY (id))
/

CREATE TABLE tab_b
   (id    NUMBER (6) NOT NULL
   ,val   NUMBER (6) NOT NULL
   ,CONSTRAINT tab_b_pk PRIMARY KEY (id))
/

INSERT INTO tab_a
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

INSERT INTO tab_b
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

Next we’ll create a package with two routines. The first routine is a function that sets the VAL value in TAB_A to a random value and returns that value. The second routine is a procedure that sets the VAL value in TAB_B using the return value of the first function:

CREATE OR REPLACE PACKAGE proc_pkg
AS
   FUNCTION val_a (p_id IN tab_a.id%TYPE)
      RETURN tab_a.val%TYPE;
   PROCEDURE update_b (p_id IN tab_b.id%TYPE);
END proc_pkg;
/

CREATE OR REPLACE PACKAGE BODY proc_pkg
AS
   FUNCTION val_a (p_id IN tab_a.id%TYPE)
      RETURN tab_a.val%TYPE
   AS
      l_return_value tab_a.val%TYPE;
   BEGIN
      UPDATE tab_a
      SET    val = TRUNC(dbms_random.value (100,200))
      WHERE  id = p_id
      RETURNING val INTO l_return_value;
      RETURN l_return_value;
   END val_a;
   
   PROCEDURE update_b  (p_id IN tab_b.id%TYPE)
   AS
   BEGIN
      UPDATE tab_b
      SET    val = val_a (id)
      WHERE  id = p_id;
   END update_b;
END proc_pkg;
/

If you look at the UPDATE_B routine, we’re updating TABL_B but since we invoke function VAL_A we’re also updating TAB_A. My initial thought when seeing code like this was “hey, that won’t work”… and it seems I was wrong:

SQL> EXEC proc_pkg.update_b (5)

PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM   tab_a
  3  WHERE  id = 5
  4  /

        ID        VAL
---------- ----------
         5        163

SQL> SELECT *
  2  FROM   tab_b
  3  WHERE  id = 5
  4  /

        ID        VAL
---------- ----------
         5        163

Okay, so it works; performing DML on one table can invoke a function that performs DML on another table. We can insert, update and delete data on a table from a PL/SQL function invoked via DML on a different table. If we were to try running a simple SELECT query using a function that does DML then we get ORA-14551:

SQL> SELECT id
  2  ,      proc_pkg.val_a (id)
  3  FROM   tab_a
  4  /
,      proc_pkg.val_a (id)
       *
ERROR at line 2:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "DEVELOPER.PROC_PKG", line 8

Yes, you can get around this particular error by making the function an autonomous transaction… but don't do that, it's not what autonomous transaction are designed for.

My problem is that I have difficulty thinking of a scenario whereby doing DML from within DML might be a good idea instead of splitting it into separate operations. In some ways it’s similar to a row level trigger and I dislike that approach to data processing as it leads to spaghetti code. Sure, some sort of demo where I want to log details about the execution of the function as it executes I can understand, but in normal business type processing…? Furthermore, keep in mind that we're essentially doing row by row type processing here; our TAB_A DML function will be invoked for each row modified in TAB_B so we're not doing bulk processing on TAB_A. This is likely to be a performance inhibitor for the TAB_A changes.

I guess this is something that I would classify as a nice feature but use only with caution when no other option exists… although I'm happy to take advice on this.

RETURNING INTO revisited

While reviewing some code I came across a feature with the RETRUNING INTO clause that I’m not sure I knew about (or I had long forgotten about). In any case it’s worth noting as a follow-up to my earlier RETURNING INTO posts…

As previously noted, RETURNING INTO is used to return scalar values back to PL/SQL from an SQL statement. The RETURNING INTO BULK COLLECT option is used for returning a set of values. The feature noted in a recent code review showed that it’s possible to use aggregate functions with the RETURNING INTO clause to collapse a multi-row operation into one or more scalar values. From a quick search of other posts on the internet, this feature crept in somewhere around Oracle 10g.

A simple example should illustrate things better. First up, we’ll create a simple table with two columns and ten rows:

CREATE TABLE t1
   (id      NUMBER (6) NOT NULL
   ,val     NUMBER (4) NOT NULL)
/

INSERT INTO t1
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

Next we’ll write a simple PL/SQL block that increments the VAL column and, using the RETURNING INTO clause, returns the SUM and AVG of the values updated:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which gives us a result of:

Sum: 65
Avg: 6.5

The sum value of 65 is (2+3+4+5+6+7+8+9+10+11), which are values the VAL column contains after the update. Obviously the average of 6.5 is the sum of 65 divided by the 10 rows that were modified.

The same applies when deleting rows:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   DELETE
   FROM   t1
   WHERE  id BETWEEN 1 AND 4
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which results in (assuming we’re operating on the original table, prior to the UPDATE above):

Sum: 10
Avg: 2.5

Note, it’s not possible to use the DISTINCT clause of the agregate functions:

DECLARE
   l_count  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING COUNT(id) INTO l_count;
 
   dbms_output.enable; 
   dbms_output.put_line ('Count: ' || TO_CHAR(l_count));
END;
/   

which results in the following error:

ERROR at line 1:
ORA-00934: group function is not allowed here
ORA-06512: at line 4

So, +1 for learning new things from code reviews. Unfortunately the developer totally spoilt it all by using something else I didn’t know about and I would definitely not recommend but that’s a topic for a different post…

String constants and package state

I have previously discussed package state and avoiding the “ORA-04068: existing state of packages has been discarded” exception. Connor McDonald has pointed out that come Oracle 11.2 constants do not create package state, except when they involve a calculation. This post expands on that by noting that string constants always create package state (thanks to my work colleague Vlad for pointing this out, after finding out the hard way…).

First up, let’s take a look at how numeric constants don’t create package state. We start with the simplest of packages compiled in session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 123;
END pkg_state;

and in another session, session 2, we run a short program that makes reference to the package constant:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || TO_CHAR(pkg_state.c_number));
END;

Not surprisingly we get the output:

Package value is: 123

If we go back to session 1 and recompile the package changing the constant to a different value:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 789;
END pkg_state;

and rerun our simple output script back in session 2 we don’t get ORA-04068 but instead our session displays the new value:

Package value is: 789

Now let’s repeat the entire exercise but with a string constant. In session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT VARCHAR2(5) := 'ABC';
END pkg_state;

Running this in session 2:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || pkg_state.c_string);
END;

we get:

Package value is: ABC

Compiling the package in session 1 to:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT CHAR(5) := 'XYZ';
END pkg_state;

gives us the following when we rerun our display routine in session 2:

BEGIN
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package "DEVELOPER.PKG_STATE"
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_STATE"
ORA-06512: at line 2

This occurs with Oracle 11.2 and 12.1. So, while in these versions have taken steps to limit constants from creating package state, Connor’s post and this post show that it doesn’t apply to all constants. Hence some care needs to be taken when planning your deployments. You wouldn’t want to be in a situation where you think you don’t have package state but in reality you do. Right Vlad?