DATE Literals

I have previously noted problems that might occur with implicit type conversion involving dates. Ad-hoc scripts are a major source of the problem. Rather than putting TO_DATE functions around date literals people tend to take the lazier approach and just specify the string literal. I’m not sure in which version it was introduced (somewhere around 11 perhaps) but Oracle has a formal wany of specifying a date literal and that format is:

DATE 'yyyy-mm-dd'

Date literals do not permit a time component to be specified. In case this needs further elaboration, an example using the EMP table would be:

SELECT *
FROM   emp
WHERE  hiredate >= DATE '1981-07-01'

to find all those employees hired on or after 1st July 1981. This is a little bit easier than TO_DATE where you will need to specify the format parameter as well as the date string itself.

Similarly, TIMESTAMP literals may be specified using:

TIMESTAMP 'yyyy-mm-dd hh2:mi:ss.ff'

or, for TIMESTAMP WITH TIME ZONE:

TIMESTAMP 'yyyy-mm-dd hh2:mi:ss.ff tzh:tzm'

A small tip perhaps but a worthwhile one.

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.

AUSOUG OTN Day 2015

I attended the Perth all-day AUSOUG OTN Day 2015 event yesterday, which was a mini-conference involving a number of excellent speakers on all things Oracle. I have noted previously how valuable I find events such as these to be and this time was no different. As per usual I came away with a better understanding on various subjects and fired up with renewed enthusiasm!

As always, a very big thank-you to the speakers (in no particular order):

Of course, a equally big thank-you must go to AUSOUG for organising the event and Oracle Corporation itself.

Changing primary key index

While investigating a recent performance issue I arrived at the conclusion that an index comprising of the primary key plus one other column would be required. I have previously noted that a unique constraint may be enforced using a non-unique index.

Having another index on the table that mirrored the primary key plus another column seemed wasteful so I set about removing the existing primary key index and replacing it with the new index. Thankfully Oracle provides the ability to alter an existing primary key constraint to be enforced through a different index without the need of dropping and recreating the constraint, as I’ll illustrate using the following set-up:

SQL>CREATE TABLE tab
  2     (id     NUMBER (6) NOT NULL
  3     ,cat    NUMBER (6) NOT NULL
  4     ,val    NUMBER (6) NOT NULL)
  5  /

Table created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_pk
  3     PRIMARY KEY (id)
  4  /

Table altered.

SQL>CREATE INDEX tab_pk2
  2  ON tab (id, cat)
  3  /

Index created.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK          UNIQUE
TAB_PK2         NONUNIQUE

In the above we have a simple table with a primary key defined on the column ID, a unique index TAB_PK on the ID column and a non-unique index on the ID and CAT colums. If we were to simply try removing the unique index that’s currently enforcing the primary key constraint then we’ll be met with:

SQL>DROP INDEX tab_pk
  2  /
DROP INDEX tab_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

What we need to do first is to tell Oracle to enforce the primary key constraint using the non-unique index:

SQL>ALTER TABLE tab
  2     MODIFY CONSTRAINT tab_pk
  3     USING INDEX tab_pk2
  4  /

Table altered.

This is now where things get a bit more interesting. If we look at the indexes on our table we find:

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK2         NONUNIQUE

Hey, what hapened to our unique index? It seems that Oracle has dropped it. So why did that happen? As it turns out, if the index is created automatically as part of the creation of the primary key constraint then it will be automatically removed when a new index is used to enforce the constraint. If the index were created before the constraint then this doesn’t happen, as illustrated below:

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

no rows selected

SQL>CREATE UNIQUE INDEX tab_pk
  2     ON tab (id)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_pk
  3     PRIMARY KEY (id)
  4     USING INDEX tab_pk
  5  /

Table altered.

SQL>CREATE INDEX tab_pk2
  2  ON tab (id, cat)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     MODIFY CONSTRAINT tab_pk
  3     USING INDEX tab_pk2
  4  /

Table altered.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME      UNIQUENESS
--------------- ---------------
TAB_PK          UNIQUE
TAB_PK2         NONUNIQUE

In the above we created the index for enforcing the primary key before declaring the constraint. When we switched to using a new index for the constraint the original index remained.

Some simple renaming is all that remains:

SQL>ALTER INDEX tab_pk
  2     RENAME TO tab_pk_old
  3  /

Index altered.

SQL>ALTER INDEX tab_pk2
  2     RENAME TO tab_pk
  3  /

Index altered.

I must say that I prefer the old index remaining behind after the change to the primary key constraint. Leaving the original index in place allows me the opportunity to revert the change in the unlikely event that the new index causes query execution plan problems. The original index can be marked as invisible:

ALTER INDEX tab_pk_old INVISIBLE

and should problems arise it can quickly be reinstated without having to do rebuild it from scratch, which is important for any reasonably large table:

ALTER INDEX tab_pk_old VISIBLE

If no problems arise after the change of primary key index then the old (now invisible) index can be dropped at some later point in time:

DROP INDEX tab_pk_old

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.