Advanced Queuing   «Prev  Next»

Lesson 6Developing P/SQL Applications for Enqueuing
Objective Set up enqueuing to the Advanced Queue

Developing PL/SQL Applications for Enqueuing

Now that you have a queue set up, you can create PL/SQL code to enqueue a message to the queue.
  • Enqueuing Message in Oracle
    To enqueue a message, you will have to use the following PL/SQL code in a procedure. This procedure will be in a PL/SQL package called PETSTORE.AQ_EX. The procedure itself will be called AQ_ENQ, and it will take an incoming parameter of data type VARCHAR2(100) called MSG_TEXT. This incoming parameter will pass the message text to the message payload.
    The body of the PL/SQL package includes the following code: 
    DECLARE 
   Enqueue_options       DBMS_AQ.enqueue_options_t; 
   Message_properties    DBMS_AQ.message_properties_t; 
   Message_handle        RAW(16); 
   Message               PETSTORE.Message_typ;

  1. You need to declare four variables necessary to enqueue a message:

  1. Enqueue_options: Allows you to set various options for enqueuing the message. You use the data type enqueue_options_t, which is defined in the DBMS_AQ package.
  2. Message_properties: Allows you to set various options for the message itself. You use the data type message_properties_t, which is defined in the DBMS_AQ package.
  3. Message_handle: Will accept a value assigned by the Advanced Queuing process.
  4. Message: The message payload you created when you created the queue.

  1. The next step is to start the procedural code by assigning the value of the MSG_TEXT parameter to the message payload: 
    	BEGIN 
	Message := Message_typ("This is a sample message);

  1. The final step in enqueuing a message is to call the ENQUEUE procedure from the DBMS_AQ package and end the procedural code: 
      DBMS_AQ.ENQUEUE(queue_name => 'queue1', 
  enqueue_options           => enqueue_options, 
  message_properties     => message_properties, 
  payload                   => message, 
  msgid                     => message_handle);  
  COMMIT; 
END;

You use each of the variables you declared at the start of the procedure. Because you will not be changing any of the options for enqueuing the message or for the message itself, you do not have to set any values for the enqueue_options or message_properties variables. The enqueuing process described above is illustrated in the following series of images.

1)The process begins with a call to the AQ_EX.AQ_ENQ procedure
1) The process begins with a call to the AQ_EX.AQ_ENQ procedure , passing a value for the message text.

2) Enqueuing 2
2) The AQ_ENQ procedure declares the variables that it will need for the message.

3) The text passed in the incoming parameter is assigned to the Message variable, which will hold the payload of the message
3) The text passed in the incoming parameter is assigned to the Message variable, which will hold the payload of the message

4) The next step is to call the ENQUEUE procedure from the DBMS_AQ package
4) The next step is to call the ENQUEUE procedure from the DBMS_AQ package

5) Enqueuing 5
5) This procedure creates a message

6) Enqueuing 6
6) assigns the appropriate values.

7) Enqueuing 7
7) and sends the message to the queue

Creating a Queue Table and Queue of Object Type

/* Creating a message type: */
CREATE type aq.Message_typ as object (
subject     VARCHAR2(30),
text        VARCHAR2(80)); 


/* Creating a object type queue table and queue: */
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.objmsgs80_qtab',
queue_payload_type => 'aq.Message_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'msg_queue',
queue_table        => 'aq.objmsgs80_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'msg_queue');

Creating a Queue Table and Queue of Raw Type 
/* Creating a RAW type queue table and queue: */
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( 
queue_table          => 'aq.RawMsgs_qtab', 
queue_payload_type   => 'RAW'); 
  
EXECUTE DBMS_AQADM.CREATE_QUEUE ( 
queue_name          => 'raw_msg_queue', 
queue_table         => 'aq.RawMsgs_qtab'); 
  
EXECUTE DBMS_AQADM.START_QUEUE ( 
queue_name          => 'raw_msg_queue');

Creating Prioritized Message Queue Table and Queue 
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.priority_msg', 
sort_list          => 'PRIORITY,ENQ_TIME', 
queue_payload_type => 'aq.Message_typ');

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'priority_msg_queue', 
queue_table        => 'aq.priority_msg');

EXECUTE DBMS_AQADM.START_QUEUE ( 
queue_name         => 'priority_msg_queue');

Creating a Multiconsumer Queue Table and Queue 
 EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => 'aq.MultiConsumerMsgs_qtab',
multiple_consumers => TRUE, 
queue_payload_type => 'aq.Message_typ');


EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => 'msg_queue_multiple',
queue_table        => 'aq.MultiConsumerMsgs_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'msg_queue_multiple');

Creating a Queue to Demonstrate Propagation 
 EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name        => 'another_msg_queue',
queue_table       => 'aq.MultiConsumerMsgs_qtab');

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => 'another_msg_queue');

In the next lesson, you will learn how to dequeue a message.

SEMrush Software