Jaa

Using Oracle Database or Oracle EBS adapter for Oracle AQ (Advanced Queuing)

  • Artikkeli

Here is a simple way to get started on Oracle AQ (Advanced Queuing) using the Oracle Database and/or Oracle E-Business Suite adapters. Here is what I did, right from defining my queue - one step at a time.

Step 1 - Define the queue payload type
CREATE TYPE AQ_MESSAGE_TYPE AS OBJECT (ID VARCHAR2(30), INFO VARCHAR2(200));

Step 2 - Define the queue table
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE(QUEUE_TABLE => 'INFO', QUEUE_PAYLOAD_TYPE => 'AQ_MESSAGE_TYPE');

Step 3 - Create the queue
EXEC DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE', QUEUE_TABLE => 'INFO');

Step 4 - Start the queue
EXEC DBMS_AQADM.START_QUEUE(QUEUE_NAME => 'AQ_MESSAGE_QUEUE');

Step 5 - Create a function to enqueue
CREATE OR REPLACE FUNCTION ENQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD IN AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
MSGID RAW(16);
OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
BEGIN
DBMS_AQ.ENQUEUE('AQ_MESSAGE_QUEUE',
OPTIONS,
PROPERTIES,
PAYLOAD,
MSGID);

 RETURN MSGID;
END;

Step 6 - Create a function to dequeue
CREATE OR REPLACE FUNCTION DEQUEUE_AQ_MESSAGE_QUEUE(PAYLOAD OUT AQ_MESSAGE_TYPE) RETURN RAW
AS
PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
OPTIONS DBMS_AQ.DEQUEUE_OPTIONS_T;
MSGID RAW(16);
NOTHING_TO_DEQUEUE EXCEPTION;
PRAGMA
EXCEPTION_INIT (NOTHING_TO_DEQUEUE, -25228);
BEGIN
OPTIONS.WAIT := DBMS_AQ.NO_WAIT;
DBMS_AQ.DEQUEUE('AQ_MESSAGE_QUEUE',
OPTIONS,
PROPERTIES,
PAYLOAD,
MSGID);
RETURN MSGID;
EXCEPTION
WHEN NOTHING_TO_DEQUEUE
THEN RETURN NULL;
END;

A word on dequeue and enqueue functions - these are simplistic in this example. You might want to modify them to take more inputs (for example - more message properties or enqueue/dequeue properties as input). The dequeue function returns immediately if a message is not available for dequeue and returns null in that case. You could tweak that too.

Step 7 - Call the functions using the adapter!
It is easy calling these from the adapter now. The reason why the DBMS_AQ.ENQUEUE and DBMS_AQ.DEQUEUE cannot be called directly from the adapter is that they use complex data types that are not supported by ODP.NET. One can now even call the dequeue function in a polling context, and get messages delivered to them as they are pushed into the queue! The links to portions of the Oracle Database adapter help that will help you do this are:

Thanks to Mustansir for suggesting this blog idea!

AqSqlScript.sql

Comments

  • Anonymous
    August 17, 2014
    Hello,thank you for the informative post.Do you have more info regarding Step 7? How to "call the dequeue function in a polling context and get messages delivered to them as they are pushed into the queue"Can I use the following to check for existing messages in PolledDataAvailableStatement ?SELECT COUNT(1)FROM   AQ$AQ_MESSAGE_QUEUE;Thank you for your advice