Create Custom Queue in Oracle and use as pre-queue to other queue

In our scenario we setup a JMS queue which will be used a pre-queue to ECX_INBOUND queue.
Below are the steps to achieve the same.

Queue Creation


1) Create a queue table
BEGIN
dbms_aqadm.create_queue_table
     ( queue_table        => 'XX_INQUEUE_TBL'
     , queue_payload_type => 'sys.aq$_jms_text_message'
     );
END;

2) Create a Queue based on queue table
    BEGIN
dbms_aqadm.create_queue
    ( queue_name  => 'XX_INQUEUE'
    , queue_table => 'XX_INQUEUE_TBL'
    );
END;

3) Start Queue. If you do no start the queue you will get error message
   as "ORA-25207: enqueue failed, queue APPS.XX_INQUEUE is disabled from enqueueing"

BEGIN
dbms_aqadm.start_queue
( queue_name => 'XX_INQUEUE'
);
END;

4) Query confirm queue and queue table. 

SELECT *
 FROM XX_INQUEUE_TBL
WHERE q_name = 'XX_INQUEUE';

SELECT *
 FROM DBA_QUEUES
WHERE name = 'XX_INQUEUE';


 Populating queue with XML message

 1) Run the block below to populate the queue
set serveroutput on;
DECLARE
l_destination_queue    VARCHAR2 (100) := 'XX_INQUEUE';
l_enqueue_options      SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
l_enqueue_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
l_enqueue_payload      SYS.aq$_jms_text_message;
x_enqueue_msgid        RAW (16);
l_xml_message CLOB := '<HEADER><NAME>Hello World</NAME></HEADER>';

BEGIN
 l_enqueue_payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct();
 l_enqueue_payload.set_text (l_xml_message);
 sys.DBMS_AQ.enqueue (
queue_name           => l_destination_queue,
enqueue_options      => l_enqueue_options,
message_properties   => l_enqueue_properties,
payload              => l_enqueue_payload,
msgid                => x_enqueue_msgid
);
 dbms_output.put_line(x_enqueue_msgid);  
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

 2) Query the queue table to verify data is populated the queue
SELECT *
  FROM XX_INQUEUE_TBL
 WHERE q_name = 'XX_INQUEUE';

The user is displayed as [SYS.AQ$_JMS_TEXT_MESSAGE]
If you want to view the payload use the query below
SELECT A.*,A.user_data.text_vc
  FROM XX_INQUEUE_TBL A
 WHERE q_name = 'XX_INQUEUE';


Once we have the data in XX_INQUEUE. We will now dequeue the data
and enqueue it into the ECX_INBOUND queue for further processing
Below a are the api's you need to enqueue the ECX_INBOUND queue.
      SYSTEM.ecxmsg     : This is required to create a payload
sys.DBMS_AQ.enqueue : This is required to enqueue the message
  to standard APPLSYS.ECX_INBOUND queue.

I will cover the above two api's in my next post.