Integration between Database Oracle AQ - Weblogic JMS Queue

To serve requirements where one needs to integrate an Oracle database to Oracle weblogic server(eg. consider a change done in database needs a change in a application too which is deployed on application server etc.) in all such cases treat below as a guideline to create Oracle AQ and to integrate it with weblogic jms queue.

Changes required in database:

1. Create new oracle user for populating message into oracle AQ

 Create the JMS user schema as a sysdba user. The user name is jmsuser and for the following example, the password is jmsuser.

file: create_user_jmsuser.sql
-- Author  : Alok M
-- Created : 23 Oct 2013
-- Purpose : create jmsuser from sysdba
Grant connect, resource TO jmsuser IDENTIFIED BY jmsuser
/
Grant aq_user_role TO jmsuser
/
Grant execute ON sys.dbms_aqadm TO jmsuser
/
Grant execute ON sys.dbms_aq TO jmsuser
/
Grant execute ON sys.dbms_aqin TO jmsuser
/
Grant execute ON sys.dbms_aqjms TO jmsuser
/

2. Creation of Oracle AQ queue and queue table

Create a queue called “TEST_ORACLE_QUEUE” with a corresponding queue table “TEST_ORACLE_QUEUE_TABLE”.
Connect as jmsuser and execute following scripts:

file: @create_queue_queuetable.sql
-- Author  : Alok M
-- Created : 23 Oct 2013
-- Purpose : PL/SQL to create TEST_ORACLE_QUEUE_TABLE & TEST_ORACLE_QUEUE from JMS User Schema

begin
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'TEST_ORACLE_QUEUE_TABLE',
                                    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
                                    storage_clause => NULL,
                                    sort_list => 'priority,enq_time',
                                    multiple_consumers => FALSE,
                                    message_grouping => DBMS_AQADM.NONE,
                                    auto_commit    => TRUE,
                                    primary_instance =>    0,
                                    secondary_instance => 0,
                                    secure => FALSE,
                                    compatible => NULL);
    
    DBMS_AQADM.CREATE_QUEUE ( queue_name => 'TEST_ORACLE_QUEUE',
                               queue_table => 'TEST_ORACLE_QUEUE_TABLE',
                               queue_type => DBMS_AQADM.NORMAL_QUEUE,
                               max_retries => NULL,
                               retry_delay => 10,
                               retention_time => 0,
                               dependency_tracking => FALSE,
                               comment    => NULL,
                               auto_commit    => TRUE );
                               
    DBMS_AQADM.START_QUEUE ('TEST_ORACLE_QUEUE', enqueue=> true, dequeue=>true);
end;
/

3. Message send utility(An Oracle package) to send messages to Oracle AQ

Connect as jmsuser and compile the package ‘PKGINTFAQJMSSENDER’ which will
send the messages to Oracle AQ.

file: PKGINTFAQJMSSENDER.h 

  
CREATE OR REPLACE PACKAGE JMSUSER.PKGINTFAQJMSSENDER
AS
-- Author  : Alok M
  -- Created : 23 Oct 2013
  -- Purpose : This package will be called from Trigger to put messages into the oracle Queue  

PROCEDURE sendJMSMessage(error_code OUT NUMBER,
                         error_text OUT VARCHAR2,
                         in_queue_name IN VARCHAR2,
                         message_type IN VARCHAR2,
                         xml_message IN CLOB); 

also compile below sql.

file: PKGINTFAQJMSSENDER.sql

CREATE OR REPLACE PACKAGE BODY JMSUSER.PKGINTFAQJMSSENDER
AS
  -- Author  : Alok M
  -- Created : 23 Oct 2013
  -- Purpose : This package will be called from Trigger to put messages into the oracle Queue
/* Enqueue to msg_queue: */
PROCEDURE sendJMSMessage(error_code OUT NUMBER,
                         error_text OUT VARCHAR2,
                         in_queue_name IN VARCHAR2,
                         message_type IN VARCHAR2,
                         xml_message IN CLOB)
AS
  v_enqueue_options dbms_aq.enqueue_options_t;  
  v_msg_props       dbms_aq.message_properties_t;  
  v_msg_id          RAW(16);  
  v_message        SYS.AQ$_JMS_TEXT_MESSAGE := SYS.AQ$_JMS_TEXT_MESSAGE.construct();  
  begin    
/*DBMS_AQ.ENQUEUE (
   queue_name          IN      VARCHAR2,
   enqueue_options     IN      enqueue_options_t,
   message_properties  IN      message_properties_t,
   payload             IN      "<type_name>",
   msgid               OUT     RAW);
*/
    v_message.set_text(xmltype(xml_message).getstringval());  
    v_message.set_string_property('messageType', message_type);
  
    dbms_aq.enqueue(queue_name         => in_queue_name  
                   ,enqueue_options    => v_enqueue_options  
                   ,message_properties => v_msg_props  
                   ,payload            => v_message  
                   ,msgid              => v_msg_id);  
    commit;  
    -- set error code for success                                             
   error_code := 0;
   error_text := null;                                              

EXCEPTION WHEN OTHERS THEN                                          
    error_code := SQLCODE;
    error_text := SQLERRM;           
end;  
END PKGINTFAQJMSSENDER;
/
GRANT ALL ON JMSUSER.PKGINTFAQJMSSENDER TO PUBLIC
/
END PKGINTFAQJMSSENDER;
/

4. Assign Privileges to working schemas

If you are working with other schemas and created jmsuser only for oracle AQ then you need to assign privileges to the schema. For example lets say you are working with 'XYZ' schema in your database, so run below sql to assign the enqueue privileges 

file: grant_enqueue_privilege_to_schema.sql

-- Author  : Alok M
-- Created : 23 Oct 2013
-- Purpose : from JMS User Schema 

EXEC dbms_aqadm.grant_queue_privilege('ENQUEUE', 
'JMSUSER.TEST_ORACLE_QUEUE', 'XYZ', FALSE);
GRANT EXECUTE ON JMSUSER.PKGINTFAQJMSSENDER TO XYZ;

BRIDGING BETWEEN ORACLE AQ AND WEBLOGIC JMS QUEUE

1. Create the data source

Go to the WL admin console under Services>JDBC>Data Sources and create a new data source ‘aqjms_DataSource’ pointing to the JMSUSER Set Driver class as ‘oracle.jdbc.xa.client.OracleXADataSource’

Change maximum capacity to 200.

2. Create the JMS Module

Go to the WL admin console under Services>Messaging>JMS Modules and create a new module
‘aqjmsmodule’

3. Create the JMS Foreign server

Under the JMS module created in the previous step create foreign server ‘aqjmsForeignServer’.
Specify oracle.jms.AQjmsInitialContextFactory as the JNDI Initial Context Factory.

4. Create the JMS Connection factory

Under the JMS Foreign server created in the previous step create connection
factory ‘aqjmsCF’.The remote JNDI name must be ‘XAQueueConnectionFactory’.
The local JNDI name must be ‘aqjms.TEST_CF’.

5. Create the JMS Queue

Under the JMS Foreign server created previously create queue destination ‘aqjmsQueue’.
The remote JNDI name must be ‘Queues/jmsuser.TEST_ORACLE_QUEUE’.
The local JNDI name must be ‘aqjms.TEST_QUEUE’.

6. Read message from JMS Queue using MDB

The above JMS queue is created under foreign JMS server and to read the messages from the JMS queue we can create an MDB on the queue.

MDB Sample Code: ReadMessageFromQueue.java
/* Author  : Alok M
Created : 23 Oct 2013
Purpose : Read message from JMS queue*/
package com.test.read;

import javax.ejb.ActivationConfigProperty;
import javax.ejb.MessageDriven;
import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.MessageListener;
import javax.jms.TextMessage;
import weblogic.javaee.MessageDestinationConfiguration;
/**
 * Message-Driven Bean implementation class for: ReadMessageFromQueue
 * 
 */
@MessageDriven(name = "com.test.read.ReadMessageFromQueue", activationConfig 
= { @ActivationConfigProperty(propertyName = "destinationType", propertyValue 
= "javax.jms.Queue")}, mappedName = "aqjms.TEST_QUEUE")
@MessageDestinationConfiguration(connectionFactoryJNDIName = "aqjms.TEST_CF")

public class ReadMessageFromQueue implements MessageListener {
/**
* @see MessageListener#onMessage(Message)
*/
public void onMessage(Message msg) {
System.out.println("### onMessage on ReadMessageFromQueue :::::::::::::::::::::::");
if (msg instanceof TextMessage) {
TextMessage bm = (TextMessage) msg;
try {
System.out.println("JMS message received on ReadMessageFromQueue " + bm.getText());
}
catch (Exception e) {
System.out.println("JMS message received with Exception on ReadMessageFromQueue ");
}
        } else {
System.out.println("Incorrect JMS message type posted.");
}
}
}
 

7. Now test the above functionality with a sample code below

Using below anonymous block above integration can be tested. It sends a message at Oracle AQ which in turn will be transferred to JMS queue and MDB deployed on JMS queue could listen the queue and retrieve the message which can be used for further processing and logic. The below block can be run from XYZ schema as it already has all the privileges. Apart from that you can also customize business logic in XYZ schema to any extent.

file: anonymous plsql block

Declare
in_queue_name VARCHAR2(100):='JMSUSER.TEST_ORACLE_QUEUE';
message_type VARCHAR2(100):='AQ$_JMS_TEXT_MESSAGE';
error_code NUMBER;
error_text VARCHAR2(200);
v_xml CLOB='put a clob type here';

-- Author  : Alok M
-- Created : 23 Oct 2013
-- Purpose : utility block to send a meesage on Oracle AQ

BEGIN

    JMSUSER.PKGINTFAQJMSSENDER.sendJMSMessage(error_code,
                                         error_text,
                                         in_queue_name,
                                         message_type,
                                         v_xml) ;                                         
END;

As now the Oracle AQ and JMS Queue are connected one should be able to read
the above message on JMS Queue using MDB deployed as an application on weblogic server.

Hope above helps in integrating database Oracle AQ and weblogic JMS queue, please leave your feedback or query.