Database Adapter With Distributed Polling Fails With ORABPEL-11624 and ORA-1786 (Doc ID 1321200.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle(R) BPEL Process Manager - Version 10.1.3.3 to 10.1.3.5.0
Oracle ESB - Version 10.1.3.3 to 10.1.3.5
Information in this document applies to any platform.
***Checked for relevance on 11-July-2013***

Symptoms

Using a 10g cluster system with a polling database adapter in BPEL or ESB, the SQL query uses the operator DISTINCT.  When deploying the BPEL or ESB process to a cluster "Distributed Polling" was selected in the JDeveloper database adapter wizard to avoid processing of the same rows by different nodes.

The use of "Distributed Polling" will add a FOR UPDATE NO WAIT clause to notify a polling node that another node currently has the row(s) locked and not to wait for release. This is a standard setup in a clustered environment. When "Distributed Polling" is selected, however, the adapter is shut down soon after endpoint activation, so the polling adapter project is not usable in a cluster.

The following error message is seen in the $ORACLE_HOME/opmn/logs/<BPEL/ESBRT container>.log file:

<2011-04-18 14:35:14,147> <ERROR> <default.collaxa.cube.activation> <Database Adapter::Inbound>
<oracle.tip.adapter.db.InboundWork handleException>
Non retriable exception during polling of the database ORABPEL-11624
DBActivationSpec Polling Exception.
Query name: [jobsLink], Descriptor name: [jobs]. Polling the database for events failed on this iteration.
[Caused by: ORA-01786: FOR UPDATE of this query expression is not allowed
]
If the cause is something like a database being down successful polling will resume once conditions change.
Caused by Exception [TOPLINK-4002] (Oracle TopLink - 10g Release 3 (10.1.3.5.0) (Build 090715)):
oracle.toplink.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-01786: FOR UPDATE of this query expression is not allowed
Error Code: 1786
Call:SELECT DISTINCT ...)) ORDER BY xxx ASC FOR UPDATE NOWAIT
bind => [xxx]
Query:AQuery(bpel___localhost_default_test_1_0__1303151713925.jobs).

at oracle.tip.adapter.db.exceptions.DBResourceException.createEISException(DBResourceException.java:483)
at oracle.tip.adapter.db.exceptions.DBResourceException.inboundReadException(DBResourceException.java:390)
at oracle.tip.adapter.db.InboundWork.handleException(InboundWork.java:763)
at oracle.tip.adapter.db.InboundWork.runOnce(InboundWork.java:701)
at oracle.tip.adapter.db.InboundWork.run(InboundWork.java:526)
at oracle.tip.adapter.db.inbound.InboundWorkWrapper.run(InboundWorkWrapper.java:43)
at oracle.tip.adapter.fw.jca.work.WorkerJob.go(WorkerJob.java:51)
at oracle.tip.adapter.fw.common.ThreadPool.run(ThreadPool.java:280)
at java.lang.Thread.run(Thread.java:595)
Caused by: Exception [TOPLINK-4002] (Oracle TopLink - 10g Release 3 (10.1.3.5.0) (Build 090715)):
oracle.toplink.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-01786: FOR UPDATE of this query expression is not allowed
Error Code: 1786
Call:SELECT DISTINCT ...)) ORDER BY xxx ASC FOR UPDATE NOWAIT
bind => [xxx]
Query:AQuery(bpel___localhost_default_test_1_0__1303151713925.jobs)
...

Changes

The database adapter may work fine in a single node environment when Distributed Polling is not selected, but when moving to a cluster environment and selecting Distributed Polling the error occurs.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms