Monday, 9 January 2012

DB Adapter polling tricks..

The commonly used polling strategies with Oracle DB adapter are:

1. DeletePollingStrategy : This is simplest where we read all possible rows from table and delete them afterwards to ensure that they are only read once.

2. LogicalDeletePollingStrategy : This is a non-intrusive polling mechanism where we update a status column to mark records as read rather than deleting them. This uses 2 SQLs -- one for polling/reading the records and another after-read SQL to update/mark the records as read.

3. LastReadId or SequencingPollingStrategy : This takes help of an external sequencing/helper table. This assumes that all new rows are inserted with an increasing key so that when selected only records with key greater than the highest key previously processed are fetched. This last highest key is stored in the helper table.

TopLink is the technology on which the DbAdapter is built and the DBAdapter UI generates some files especially toplink metadata or -mappings.xml file which can be hand edited to extend the polling mechanisms.

There maybe cases where you would like to use logical delete with custom SQLs instead of Toplink generated SQL to poll DB tables.In order to achieve this please make sure below steps are followed:

1. Pure SQL will not be executed by Toplink if you choose the second option (Logical Delete) as the After Read Strategy in Adapter Configuration Wizard.
2. To make Pure SQL work with Logical Delete Strategy, you must choose the first option (Delete the rows) as the After Read Strategy in Adapter Configuration Wizard, then edit the toplink project directly by adding two custom SQLs, one for polling, the other for after reading operations.
3. For editing the toplink project open the -or-mappings.xml outside of Jdeveloper (the file is write protected) and add below entries:
<toplink:call xsi:type="toplink:sql-call">
<toplink:sql>Enter your SQL here</toplink:sql>
When adding the <toplink:call> element, it must be under <opm:query>. Also immediately after <opm:queries> and before <opm:querying> add this element:
<toplink:delete-query xsi:type="toplink:delete-object-query">
<toplink:call xsi:type="toplink:sql-call">
<toplink:sql>Enter your After read SQL here</toplink:sql>
There maybe cases where you would like to control the number of DB records which are polled at a time. The DB adapter wizard gives several configurable properties which can control this.

1. Polling frequency is the interval at which the DB adapter activation agent polls the new records.

2. Database Rows per Transaction (default value of 10) controls the number of records which are read at a time. For eg. if there are 1000 records to be read and we set the Database Rows per Transaction=10 , at the start of the polling interval the entire work is divided into 1000/10=100 transaction units and completes sequentially till all are processed. This property resolves to MaxTransactionSize in the jca file.

3. If we enable the distributed polling checkbox and set the MaxTransactionSize the behaviour changes. Here the entire work is divided into 100 transaction units but each unit is processed in a single polling interval i.e 1st polling interval 10 records are processed, rest 990 will be processed in subsequent intervals.

Friday, June 11, 2010

BINDING.JCA-12561 JCA Resource Adapter location error (WebLogic)

After creating datasources in Weblogic Admin console, at runtime the composites may fail with below error :

[ERROR] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: weblogic] [ecid: 0000I^bkZHaB_6BzfWZf451C4_ij000009,0] [APP: soa-infra] JCABinding=> XYZInterfaces [[
JCA Resource Adapter location error (WebLogic).
Unable to locate the JCA Resource Adapter via .jca binding file element
The JCA Binding Component is unable to startup the Resource Adapter specified in the element: location='eis/DB/MSSQL'.
The reason for this is most likely that either
1) the Resource Adapters RAR file has not been deployed successfully to the WebLogic J2EE Application server or
2) the JNDI setting in the WebLogic JCA deployment descriptor has not been set to eis/DB/MSSQL. In the last case you might have to add a new 'connector-factory' entry (connection) to the deployment descriptor.
Please correct this and then restart the WebLogic Application Server

If your jdbc (jdbc/MSSQL) settings are correctly configured and test configuration works fine then there are few things which can be checked to avoid this error:

1. Login to Weblogic Administration Console, and under Deployments, make sure your Database Adapter is in active state. If it is in some other state like "Prepared" make it "Active" by selecting the Database Adapter and clicking on Start->Service all requests.

2. Also make sure your JNDI name (eis/DB/MSSQL) is created properly under Deployments->DB Adapter->Configuration->Outbound Connection Pools. Ensure the JDBC datasource (jbdc/MSSQL) exists under eis/DB/MSSQL.

After above changes are made, activate the changes on console and redeploy the DB adapter. Bounce the admin and managed servers and redeploy your BPEL process.

Weblogic Managed Server goes down with java.lang.OutOfMemoryError: Java heap space

This is a short post covering how to set heap space in weblogic server(10.3.2) . Incase the weblogic managed server goes down with java.lang.OutOfMemoryError: Java heap space or java.lang.OutOfMemoryError: GC overhead limit exceeded you need to increase the heap space settings.

Modify the below file to increase the heap space.

Add the environment variable USER_MEM_ARGS
USER_MEM_ARGS="-Xms4G -Xmx8G -XX:PermSize=256m -XX:MaxPermSize=512m"
Setting this variable overwrites any default java memory arguments passed during startup.Once done restart the admin and managed servers for the change to take effect.
For starting admin server :
For starting managed server:

Wednesday, June 9, 2010

Invoking SAP BAPIs from Oracle BPEL

The Oracle AS Adapter for SAP (SAP Adapter) exposes the SAP BAPIs/iDocs as a service via WSDL with JCA binding. The Application Explorer is used to configure the SAP Adapter. WSDL files can be created for both JCA Outbound (Request-Response service – BPEL invoke) and JCA Inbound (Event Notification – BPEL receive) Interactions and saved to a specific directory on server.The Jdeveloper based design-time wizard has a WSIF browser that is launched from the BPEL Partner Link activity to explore the WSDLs generated by the Adapter Application Explorer.

This post describes how to configure the SAP Adapter to generate WSDL out of a SAP BAPI and use it inside an Oracle BPEL process.

Application Explorer Configuration :

Create a new JCA project and this name will be later used while configuring connection factory in oc4j-ra.xml/ra.xml(for weblogic)

Provide the SAP System login credentials.

As you can see below once the connection to SAP has been established we can browse through the BOR(Business Object Repository) and select a particular BAPI Interface and generate WSDL from it for integration purpose.

In 11g apart from the .wsdl file 2 more files are generated, one a .jca file and other .xsd (with schema for request-response) in $SOA_HOME/soa/thirdparty/ApplicationAdapters/wsdls directory.
Jdeveloper Configuration :
In Jdeveloper create a BPEL process and add a partner link. Using service explorer tab in the partner link configuration search for the WSDL generated from Application Explorer under adapters section. 

Note for SOA 11G, in order to access the BAPI WSDLs (or similar JCA adapter files)you need a third party adapter service which comes with Jdeveloper version . So make sure you have this version of Jdev for development.

Also in 11G you need to copy the *.wsdl, *.jca and *.xsd files to the local machine for configuring the third party adapter service.
Once the above changes are done, before running the BPEL process you have to modify the oc4j-ra.xml/ra.xml(for weblogic) and make sure the property IWayConfig is set to the JCA Project name which we created earlier in Application Explorer. Bounce the BPEL/ WLS server and run the BPEL process.

Tuesday, June 8, 2010

Large SAP BAPIs failing with java.lang.OutOfMemoryError

OutOfMemory errors are commonly seen while processing large BAPIs, where XML data returned from the SAP Adapter is huge(eq. >10MB).

While processing the BPEL process either fails at the transform phase with “java.lang.OutOfMemoryError: Java heap space” Or the BPEL process doesn’t instantiate and you see errors like below in the console "java.lang.IllegalStateException: Commit called with no active transaction."

Common log files to be checked for these errors are :

1. OPMN : $ORACLE_HOME\opmn\logs\default_group~oc4j_soa~default_group~1.log
2. OC4J: $ORACLE_HOME\j2ee\OC4J\logs\oc4j\log.xml
3. Adapter : $ORACLE_HOME\adapters\application\config\targetname\logs\jca_xxx.log


1. Increase the heap size of server. Modify the opmn.xml and increase the java start-parameters for oc4j_soa. Example: -–Xmx2048M –Xms1024M -XX:MaxPermSize=256M
While processing the BPEL process either fails at the transform phase with “java.lang.OutOfMemoryError: Java heap space” Or the BPEL process doesn’t instantiate and you see errors like below in the console "java.lang.IllegalStateException: Commit called with no active transaction."
Common log files to be checked for these errors are :
1. OPMN : $ORACLE_HOME\opmn\logs\default_group~oc4j_soa~default_group~1.log
2. OC4J: $ORACLE_HOME\j2ee\OC4J\logs\oc4j\log.xml
3. Adapter : $ORACLE_HOME\adapters\application\config\targetname\logs\jca_xxx.log
Solution: 1. Increase the heap size of server. Modify the opmn.xml and increase the java start-parameters for oc4j_soa. Example: -–Xmx2048M –Xms1024M -XX:MaxPermSize=256M
2. Adjust transaction-timeout settings. This can be done at 3 places : syncMaxWaitTime in BPEL Console, transaction-timeout parameter in $Oracle_Home\j2ee\home\config\transaction-manager.xml and transaction-timeout parameter in $Oracle_Home\j2ee\home\application-deployments\orabpel\ ejb_ob_engine\orion-ejb-jar.xml
3. Also set "Audit Trail Logging Level" from BPEL Console to "minimal".
4. Bounce the SOA server for changes to take effect and retest the issue.


  1. thank you so much but while i am trying to do the same as you advised in step3 , i keep seeing this error in my jdev "Nameprefix toplink used but not declared" . could you help me here please

  2. Hi,

    I have a requirement where I have to poll data from 3 tables at a time where one is parent and the other two are child tables. They are related to each other by primery key and foreign key. Here I have to use one custom sql select query too under db adaptar mapping file.

    I used delete the rows option during db adaptar configuration and under query section add my custom sql query. Under delete query section i have added one update statement which will update one field of the parent table.

    I am facing problem here when db adaptar polling data. as soon as it completes in child table I am not able to find any data. Data are truncating from child table. Though parent table have data and the updated field too.

    What to do?