Oracle DBMS_AQ.dequeue sample code

--------------------------------------------------------------------------------------------------------------------
-- Following is a sample code on how to dequeue and insert into a custom table from AQ table:-
--------------------------------------------------------------------------------------------------------------------
DECLARE

   r_dequeue_options       DBMS_AQ.dequeue_options_t;
   r_message_properties   DBMS_AQ.message_properties_t;
   v_message_handle        RAW (16);
   o_payload                     xx_custom_payload_obj;
   lqname                          VARCHAR2 (30 BYTE);
   l_userdata                      XMLTYPE;
   l_usertype                     ANYDATA;
   l_msgid                         RAW (16);
   l_clob                            CLOB;
   l_corrid                         VARCHAR2 (128 BYTE);
   l_recid                           NUMBER := &p_recid;
  
BEGIN
   SELECT q_name,user_prop, msgid, corrid
     INTO lqname,l_usertype, l_msgid, l_corrid
     FROM xx_custom_tbl
    WHERE msgid = &p_msgid;

   r_dequeue_options.dequeue_mode := DBMS_AQ.remove;
   r_dequeue_options.msgid := l_msgid;
   r_dequeue_options.navigation := DBMS_AQ.next_message;
   r_dequeue_options.deq_condition := l_corrid;

   DBMS_AQ.dequeue (queue_name              => lqname,
                                       dequeue_options       => r_dequeue_options,
                                       message_properties   => r_message_properties,
                                        payload                     => l_userdata,
                                        msgid                        => v_message_handle
                                      );

   SELECT XMLTYPE.getclobval (l_userdata)
        INTO l_clob
      FROM DUAL;

   INSERT INTO xx_custom_xml
               (rec_id, file_name, file_struct, xml_data, msgid
               )
        VALUES (l_recid, 'DQTESTFILE.XML', l_clob, l_userdata, l_msgid
               );

   COMMIT;
  DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || o_payload.message || '] ***');

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      ROLLBACK;
END;
--------------------------------------------------------------------------------------------------------------------