Advanced Queuing hello world

First for those who are looking for the difference between Queuing, Advanced Queuing (AQ) and Streams Advanced Queuing, there is none. There is no Beginner Queuing and Advanced Queuing was renamed to Streams advanced queuing when streams was popular and renamed back to Advanced Queuing was streams was deprecated.

I am the provider, I give you messages to reads. I do not need to wait you to read the first one before I can send you the next one.

You are the receiver, you read them one by one, whenever you have time.

First a few grants

SQL> grant execute on dbms_aq to scott;
Grant succeeded.
SQL> grant execute on dbms_aqadm to scott;
Grant succeeded.
SQL> grant execute on dbms_aqin to scott;
Grant succeeded.

The queue table and queue

SQL> EXEC dbms_aqadm.create_queue_table('QT', 'SYS.AQ$_JMS_TEXT_MESSAGE')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.create_queue('Q','QT')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.start_queue('Q')
PL/SQL procedure successfully completed.

You start listening, if there is nothing to read, you wait

SQL> set serverout on
  2     dequeue_options            DBMS_AQ.DEQUEUE_OPTIONS_T;
  3     message_properties         DBMS_AQ.MESSAGE_PROPERTIES_T;
  4     message_handle             RAW (16);
  5     msg                        SYS.AQ$_JMS_TEXT_MESSAGE;
  6  BEGIN
  7    DBMS_AQ.dequeue (
  8      queue_name           => 'Q',
  9      dequeue_options      => dequeue_options,
 10      message_properties   => message_properties,
 11      payload              => msg,
 12      msgid                => message_handle);
 14    COMMIT;
 15  END;
 16  /

It is waiting

I write a first message.

  2    enqueue_options      DBMS_AQ.ENQUEUE_OPTIONS_T;
  3    message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
  4    message_handle       RAW (16);
  5    msg                  SYS.AQ$_JMS_TEXT_MESSAGE;
  6  BEGIN
  7    msg := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  8    msg.set_text('HELLO PLSQL WORLD !');
 10      queue_name           => 'Q',
 11      enqueue_options      => enqueue_options,
 12      message_properties   => message_properties,
 13      payload              => msg,
 14      msgid                => message_handle);
 15    COMMIT;
 16  END;
 17  /

PL/SQL procedure successfully completed.

Now you receive it !

PL/SQL procedure successfully completed.

I write one from Java using Java Message Service (JMS)

import java.util.Properties;
import java.sql.*;
import javax.jms.*;
import oracle.jms.*;

public class JMS {
  public static void main(String argv[]) throws JMSException, SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=SRV01)(Port=1521))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "SCOTT");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    java.sql.Connection conn = DriverManager.getConnection(url, props);
    QueueConnection qconn = AQjmsQueueConnectionFactory.createQueueConnection(conn);
    QueueSession qsess = qconn.createQueueSession(true, 0);
    Queue q = qsess.createQueue("Q");
    QueueSender qsend = qsess.createSender(q);
    TextMessage msg;
    msg = qsess.createTextMessage("TEST JAVA");
    qsend = qsess.createSender(q);

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/xdk/lib/xmlparserv2.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/aqapi.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/jmscommon.jar
$ export CLASSPATH
$ javac
$ java JMS

ojdbc5 is for java5

Now you read the next message (and you do not have to wait, there is one in the queue)

SQL> /
PL/SQL procedure successfully completed.

Read more in the Advanced Queuing User’s Guide

Published by

Laurent Schneider

Oracle Certified Master

One thought on “Advanced Queuing hello world”

  1. The particular tools and techniques used in a business succession plan will vary based on the
    goals and objectives of the four groups affected
    by the plan: the senior generation business owner, the junior generation family members involved in the business, key
    non-family employees, and family members not involved in the business.
    Caring For A Relative With Brain Damage: Make Sure You Have Support.
    Trust is also a compulsory requirement for all fulfilling and happy relationships.

Leave a Reply