17 March 2022

ORA-24017: cannot enable enqueue on QUEUE, %s is an exception queue

Generating documents can take quite some time, which I prefer to do in the background. To facilitate this we use Advanced Queueing (DBMS_AQ). We place the orders that we want to have a document for in a Queue. There is a callback function on the Queue which will take care of the document generation (we use APEX Office Print).
If the above is abacadabra to you, I suggest you read up on DBMS_AQ and callback functions on this blog by Adrian Billington.

Normally this works flawlessly, until recently when there was a typo in the URL to call AOP.
When the callback function would try to call AOP, it was unable to do so. Eventually this would cause that the message in the Queue would be moved to the Exception Queue.

So now we have all these messages in the Exception Queue and still no documents. The plan was to Dequeue the messages from the Exception Queue to the Normal Queue and have them processed again, of course after the typo was corrected.
By default it is not possible to Dequeue messages from the Exception Queue. So step one would be to enable Dequeue for the Exception Queue.

First step: find out the name of the Exception Queue:

  SQL> select name
  2        ,queue_table
  3        ,queue_type
  4    from user_queues
  5* /

                       NAME              QUEUE_TABLE         QUEUE_TYPE 
___________________________ ________________________ __________________ 

Next step, enable the Dequeue on the Exception Queue:

SQL> begin
  2     dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
  3                            ,dequeue    => true
  4                            );
  5  end;
  6* /

Error starting at line : 1 in command -
   dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
                          ,dequeue    => true
Error report -
ORA-24017: cannot enable enqueue on QUEUE, AQ$_ORDER_DOCUMENTS_QUEUE_E is an exception queue
ORA-06512: at "SYS.DBMS_AQADM", line 747
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8626
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 124
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8540
ORA-06512: at "SYS.DBMS_AQADM", line 742
ORA-06512: at line 2
24017. 00000 -  "cannot enable enqueue on QUEUE, %s is an exception queue"
*Cause:    User tried to enable enqueueing to an exception queue.
*Action:   None.

However this doesn't work, as you can see above.
As I didn't read the error message properly, I thought it said that Dequeue was not allowed on an Exception Queue, but it was complaining about the Enqueue.
The default value for the Enqueue argument in DBMS_ADADM.START_QUEUE is true, and this is not allowed.

SQL> begin
  2     dbms_aqadm.start_queue (queue_name => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
  3                            ,enqueue    => false
  4                            ,dequeue    => true
  5                            );
  6  end;
  7* /

PL/SQL procedure successfully completed.

Including the Enqueue argument in the call to DBMS_AQADM.START_QUEUE remedies this.

Now it is trivial to Dequeue from the Exception Queue and Enqueue on the Normal Queue:

   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     raw(16);
   l_payload            document_queue_ot;
   l_enqueue_options    dbms_aq.enqueue_options_t;
   for r in (select q.msgid
               from order_documents_queue q
              where q_name = 'AQ$_ORDER_DOCUMENTS_QUEUE_E')
      l_dequeue_options.msgid := r.msgid;
      dbms_aq.dequeue (queue_name         => 'AQ$_ORDER_DOCUMENTS_QUEUE_E'
                      ,dequeue_options    => l_dequeue_options
                      ,message_properties => l_message_properties
                      ,payload            => l_payload
                      ,msgid              => l_message_handle
      dbms_aq.enqueue (queue_name         => 'documents_queue'
                      ,enqueue_options    => l_enqueue_options
                      ,message_properties => l_message_properties
                      ,payload            => l_payload
                      ,msgid              => l_message_handle
   end loop;

After the processing of the Normal Queue, there were still a few messages that popped back into the Exception Queue. After careful examination, it was determined that they could be purged from the Exception Queue:

   po_t dbms_aqadm.aq$_purge_options_t;
   dbms_aqadm.purge_queue_table ('ORDER_DOCUMENTS_QUEUE'
                                ,'qtview.queue = ''AQ$_ORDER_DOCUMENTS_QUEUE_E'''
                                , po_t


02 March 2022

Modify a JSON structure with a single Update

Recently there was a question on the Oracle Community Forum related to JSON, more specifically how to change the structure of it. The link to the Question is at the bottom of this post.
The starting point is the following JSON:

   "tktKey": "123456",
   "attlist1": [
         "attkey": "123",
         "attval": "abc"
         "attkey": "456",
         "attval": "def"
   "attlist2": [
         "attkey": "098",
         "attval": "xyz"
The task at hand was to combine both attlist1 and attlist2 into a single attlist.
To do this the JSON object that is contained in attlist2 need to be extracted and place in attlist1. After that the element attlist2 can be removed from the structure. When this is done, there is an element with attlist1 with three JSON objects in it. Finally the element attlist1 needs to be renamed to attlist.
All of these actions can be done with a single SQL statement using JSON_TRANSFORM.

Let's first create a table to hold the original JSON
   SQL> create table js
  2  (str clob 
  3  )
  4* /

Table JS created.
And insert the JSON Object in there.
   SQL> insert into js values ('{ "tktKey": "123456", "attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ],  "attlist2": [{"attkey": "098", "attval": "xyz"  } ]}')
  2* /

1 row inserted.
Here comes the magic moment:
   SQL> update js
  2     set str= json_transform
  3                (js.str
  4                ,append '$.attlist1'
  5                    = json_query (js.str, '$.attlist2[*]')
  6                ,remove '$.attlist2'
  7                ,rename '$.attlist1' ='attlist')
  8* /

1 row updated.
With JSON_QUERY (line 5) I extract the JSON Object from attlist2, which is added (APPEND, line 4) to attlist1.
Attlist2 is removed from the JSON object (line 6) and finally on line 7 attlist1 is renamed to attlist.

And to view the results, in a nice readable format:
SQL> select json_query (j.str, '$' returning clob pretty) as result
  2    from js j
  3* /

  "tktKey" : "123456",
  "attlist" :
      "attkey" : "123",
      "attval" : "abc"
      "attkey" : "456",
      "attval" : "def"
      "attkey" : "098",
      "attval" : "xyz"

The more I use the JSON functionality that the Oracle database has to offer, the more I like them!