Sunday, April 20, 2014

Using Oracle AQ with Oracle SOA 11g

Oracle Advanced Queuing(AQ) is Oracle Database message queuing mechanism which acts as the queuing infrastructure.  With AQ the different applications can communicate and exchange the message via Oracle database queues.  Oracle AQ provides the API to enqueue and dequeue the messages to and from the queues in Oracle database.

Oracel AQ is the integral part of Oracle database already.  In this tutorial Oracle XE 10g is used.   Oracle AQ is available from two built-in PL/SQL packages in Oracle database:

 package provides PL/SQL procedures to manage the AQ configuration and administration.  Some basic functionality in DBMS_AQADM are like: Create the queue table, create the queue, start the queue, stop queue.

 package provides the interface to other applications to AQ.  DEQUEUE and ENQUEUE are two procedures for enqeuing and deqeuing the AQ queue.

After one AQ queue is created and started the application such as PL/SQL procedures can enqueue the message  into the queue and other application such as one SOA component can dequeue the message from this queue.

The below are the steps showing how one AQ queue is created from the scratch and how a message is enqueued in the queue and etc.  After these the steps of creating one SOA component to dequeue the message from AQ queue.

Setup AQ queue in Oracle XE 10g

There are several steps in setting up one AQ queue in Oracle database:

1.     Create one user in database.  This user will be granted with the roles: AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE.  Log into Oracle XE 10g as the sysdba and then create one user called EVENT_USER and then grant certain privileges to this user.

sqlplus sys@localhost:1521/xe as sysdba
create user EVENT_USER identified by oracle;
grant create session, resource, AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE to EVENT_USER;
grant execute on DBMS_AQADM to EVENT_USER;
grant execute on DBMS_AQ to EVENT_USER;

2.     Create a queue table.  Before one type called invoice_type is created.  This type is used as the payload type of the message in AQ queue.   

create or replace type invoice_type as object(
      invoice_id number,
      invoice_number varchar2(60),
      vendor_id number

              queue_table => 'EVENT_USER.invoice_queue_table', 
              queue_payload_type => 'EVENT_USER.invoice_type');

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;
3.     After the queue table is created create a queue
          queue_name =>'EVENT_USER.invoice_queue',              

select name, queue_table, queue_type from user_queues;
After the execution of above PL/SQL scripts some database objects are created for this AQ queue as shown below. 

4.     Last step is to start the queue.  It can be done by executing the below procedure from DBMS_AQADM.
exec DBMS_AQADM.START_QUEUE('EVENT_USER.invoice_queue');

Enqueue AQ queue with PL/SQL package
In this example one PL/SQL procedure from DBMS_AQ package is used to enqueue the AQ queue.

  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties       dbms_aq.message_properties_t;
  message_handle     RAW(16);
  message           invoice_type ;
  message_id         NUMBER;

 message := invoice_type (10, 'ABCD123', 80);
 enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;
  enqueue_options.SEQUENCE_DEVIATION := null;
  message_properties.EXPIRATION := DBMS_AQ.NEVER;

    queue_name => 'invoice_queue',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);

Consume the message from AQ queue from Oracle SOA component
Oracle SOA component(BPEL and Mediator) can  consume the message from AQ queue(dequeue) or produce the message into the queue(enqueue). It is done by using Oracle SOA uses JCA adapter for AQ.

In order to use AQ adapter some configurations need to be done.
1.     Create the data source in Weblogic console via Domain Structure->Services->Data Sources.  
This data source will point to the Oracle database where AQ queue resides on.  In this example it is Oracle XE 10g running on local machine. Here AQEvent data source is created and its JNDI name is set as jdbc/AQEvent

2.     Create AQAdpater instance in Weblogic console via Domain Structure->Deployments->Deployments->AQAdapter.  
An new AQ adapter instance is created as eis/AQ/LocalEventConnection whose XADataSourceName is set as: jdbc/AQEvent.

3.     Use AQAdapter in JDeveloper
Here the AQAdapter will be used as the service to consume the message from the AQ queue specified in adapter jca file.  This jca file also specifies the AQ connection factory which is configured as eis/AQ/LocalEventConnection in the above step.

<adapter-config adapter="AQ Adapter" name="AQEventService" wsdllocation="AQEventService.wsdl" xmlns="">
  <connection-factory location="eis/AQ/LocalEventConnectionen" uiconnectionname="LocalEventConnectionen">
  <endpoint-activation operation="Dequeue" porttype="Dequeue_ptt">
    <activation-spec classname="">
      <property name="SchemaValidation" value="false">
      <property name="QueueName" value="INVOICE_QUEUE">
      <property name="DatabaseSchema" value="EVENT_USER">


After the SOA project is deployed to the server, the AQAdapter will start to poll the message in AQInvoice queue.

Thursday, March 13, 2014

DbAdapter instance is not setup correctly in cluster environment

Recently I had the issue one Oracle SOA DbAdapter instance in the cluster environment.   The DbAdapter is created in the single node development environment.  Everything is working fine but after moving to testing environment which is cluster environment.   DbAdapter is created following the same steps as did for development environment.  Things start to go wrong sometimes.  The faults occurred complains that
the Resource Adapters RAR file has not been deployed successfully to the WebLogic Application server or
element in weblogic-ra.xml has not been set to  the created DbApater instance.  However the DbApater instance looks completely correct from Weblogic Console.   After some while of investigation it is realized that it might be the resource adapter plan file is not set up correctly in both nodes or one of node.  Checking the Plan.xml files on each node verifies this.   After copying the Plan which is set correctly to another node everything starts to work fine.

There is one wonderful blog which explains this very clearly.

So next time when you are working with resource adapter in cluster environment keep this in mind. 

Tuesday, November 19, 2013

Installing Oracle WebCenter Content 11g

In this blog I would like to briefly show the steps to install Oracle WebCenter Content 11g.  Oracle WebCenter 11g is based on Weblogic server now and they also need database schemas created just as other Oracle Fusion applications.

I will install them in my laptop with Windows 7 and use Oracle XE 10g as the database where the schemas reside. 

You can download Oracle XE, Weblogic 10.3.6 and RCU from this url:

You can download Oracle WebCenter Content from the url:

Step 1: Install Weblogic 

If you already have Weblogic installed you can skip this step.

My laptop runs on Windows 7 64-bit so the download of Oracle Weblogic 10.3.6 is wls1036_generic.jar.  To start the installer just use the following command.

 java -jar wls1036_generic.jar

After the successful installation there is new folder as the below:

Step 2: Install database schemas using RCU 

My RCU download is  Unzuip this file into the folder RCU and run the following command.  Here it is assumed that Oracle XE 10g is installed already. 



 cd RCU/rcuHome/BIN 


After RCU installation is done, several schemas are created in Oracle XE. 

Step 3: Install Oracle WebCenter Content 11g

The download of WebCenter Content 11g are two zip files: and
Unzip two files into the folders: Disk1 and Disk2.
Use the following command to 

cd C:\MyDownload\OracleWebCenter\Disk1 

setup -jreLoc C:\Java\jdk1.7.0_06

Oracle Middleware Home is: C:\Oracle\Middleware and Oracle Home Directory is: Oracle_ECM1.

After the installation there will be one Oracle_ECM1 folder created under Oracle Middleware.

Step 4: Create WebCenter Content Domain

In this step one domain for WebCenter Content will be created.   Use the following command to start Conifguration Wizard.


Choose the products: WebCenter Content and WebCenter Imaging.

The domain name is: ecm_domain.

Change the schema owner name if they are not correct.

Create the cluster whose name is: WCC_Cluster.

Create the machine whose name is: LocalMachine.

Step 5: Start WebCenter Content 11g

Now WebCenter Content 11g can be started.
This command is used to start the Admin server.


These commands are used to start the managed  servers.

C:\Oracle\Middleware\user_projects\domains\ecm_domain\bin\startManagedWebLogic.cmd UCM_server1 http://localhost:7001
C:\Oracle\Middleware\user_projects\domains\ecm_domain\bin\startManagedWebLogic.cmd IPM_server1 http://localhost:7001
C:\Oracle\Middleware\user_projects\domains\ecm_domain\bin\startManagedWebLogic.cmd IBR_server1 http://localhost:7001