create and post message to AQ with XMLTYPE as the payload type.

This post shows how to create an AQ with XMLTYPE as the payload type.
This is useful when the payload structure is not known or DB guys do not want to define objects and just go with XMLTYPE.
It can accept any xml request, which can then be consumed and transformed accordingly in SOA for further use.

I assume you already have privileges to create AQ. (If not then refer this post to grant privileges.)


--Queue Table
BEGIN
 DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => 'QT_SampleQueue', Queue_payload_type => 'SYS.XMLTYPE', Sort_list => 'ENQ_TIME', COMMENT => 'A sample queue table');
END;

--Queue
BEGIN
DBMS_AQADM.CREATE_QUEUE (
queue_name =>'SampleQueue',
queue_table=>'QT_SampleQueue');
END;


SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;


exec DBMS_AQADM.START_QUEUE('SampleQueue');




--Post request
DECLARE
 
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_id RAW(16);
  message SYS.XMLType;
 
BEGIN
 
  message := sys.XMLType.createXML('<catalog>

   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description> 

</book></catalog>');

  DBMS_AQ.ENQUEUE( queue_name => 'SampleQueue',
                   enqueue_options => queue_options,
                   message_properties => message_properties,
                   payload => message,
                   msgid => message_id);
  COMMIT;
END;

Generate a Unique ID to be used as Correlation for Asynchronous flows in SOA

In case of asynchronous flow, sometimes we need a unique ID to track the flow across different system or different services. we can use the XSLT function oraext:generate-guid() to generate the ID.

In below example I am defining a variable as assigning the unique ID to it, and later restructuring.
you can also directly use it in a XSLT transformation.


      <variables>
        <variable name="correlationID" type="xsd:string">
          <from>oraext:generate-guid()</from>
        </variable>
      </variables>  

        <assign name="Assign_ServiceRequestDetails">
          <copy>
            <from>concat(substring($correlationID,1,8),'-',substring($correlationID,9,4),'-',substring($correlationID,13,4),'-',substring($correlationID,17,4),'-',substring($correlationID,21))</from>
            <to>$serviceRequest/ns14:CorrelationID</to>
          </copy>
       </assign>

After restructuring in assign:

This id can be use in all the subsequent bpel processes to track the flow.