SOA Database Adapter Performing a Select Cannot Return a Large Number of Rows

(Doc ID 1274942.1)

Last updated on MARCH 08, 2017

Applies to:

OracleAS Adapters - Version 10.1.3 to 10.1.3.4 [Release AS10gR3]
Information in this document applies to any platform.
***Checked for relevance on 03-Dec-2014***

Symptoms

Using a database adapter to read rows from a table doing a Select on SOA 10.1.3.x does not successfully return all of the data when there are a large number of rows to return, e.g. several thousand rows. 

The database adapter that does the Select on a table intends to return the result to BPEL or ESB. When there is some lower number of rows returned the data is returned fine. When the number of rows increases to a certain value, that is several thousand rows, faults and timeout errors are seen.  Timeout errors may still be seen even when setting the BPEL, ESB, HTTP and Container timeouts to large values.  You may also see the JVM run at a high level of CPU when running the instance that tries to return the large number of rows.

Some examples of what may be observed:

  1. A very long time to process all of the data, causing timeouts unless the timers are set very high.
  2. High CPU usage as the container struggles to process all of the data
  3. A possible out of memory condition
  4. A mangling or corruption of the data returned due to heavy parsing
  5. Appearance that nothing is happening, that the BPEL and/or ESB instance is just "hanging"

Examples of errors that may be seen are as follows, these are only examples, different errors may also be encountered:

If ESB is used, the ESB Runtime log.xml, note the rollback timeout:

<MESSAGE>
  <HEADER>
    <TSTZ_ORIGINATING>2010-12-02T07:12:10.001-05:00</TSTZ_ORIGINATING>
    <COMPONENT_ID>webservices</COMPONENT_ID>
    <MSG_ID>OWS-03005</MSG_ID>
    <MSG_TYPE TYPE="WARNING"></MSG_TYPE>
    <MSG_LEVEL>1</MSG_LEVEL>
    <HOST_ID>test.com</HOST_ID>
    <HOST_NWADDR>10.10.10.10</HOST_NWADDR>
    <MODULE_ID>client</MODULE_ID>
    <THREAD_ID>28</THREAD_ID>
    <USER_ID>oracle</USER_ID>
  </HEADER>
<CORRELATION_DATA>
 <EXEC_CONTEXT_ID><UNIQUE_ID>10.10.10.10:41205:1291291930002:9</UNIQUE_ID><SEQ>0</SEQ></EXEC_CONTEXT_ID>
</CORRELATION_DATA>
<PAYLOAD>
  <MSG_TEXT>Unable to connect to URL: http://test.com:7777/event/Item due to java.security.PrivilegedActionException: oracle.j2ee.ws.saaj.ContentTypeException: Not a valid SOAP Content-Type: text/html; charset=iso-8859-1</MSG_TEXT>
</PAYLOAD>
</MESSAGE>


<MESSAGE>
<HEADER>
<TSTZ_ORIGINATING>2010-12-02T07:12:58.258-05:00</TSTZ_ORIGINATING>
<COMPONENT_ID>tip</COMPONENT_ID>
<MSG_TYPE TYPE="ERROR"></MSG_TYPE>
<MSG_LEVEL>1</MSG_LEVEL>
<HOST_ID>test.com</HOST_ID>
<HOST_NWADDR>10.10.10.10</HOST_NWADDR>
<MODULE_ID>esb.server.dispatch.agent</MODULE_ID>
<THREAD_ID>31</THREAD_ID>
<USER_ID>oracle</USER_ID>
</HEADER>
<CORRELATION_DATA>
<EXEC_CONTEXT_ID><UNIQUE_ID>10.10.10.10:41205:1291291978258:12</UNIQUE_ID><SEQ>0</SEQ></EXEC_CONTEXT_ID>
</CORRELATION_DATA>
<PAYLOAD>
<MSG_TEXT>Failed to commit transaction</MSG_TEXT>
<SUPPL_DETAIL><![CDATA[oracle.tip.esb.server.common.exceptions.BusinessEventRetriableException: Failed to commit transaction; transaction status is "6"


at oracle.tip.esb.server.common.JTAHelper.commitTransaction(JTAHelper.java:178)
at oracle.tip.esb.server.dispatch.agent.ESBWork.run(ESBWork.java:155)
at oracle.j2ee.connector.work.WorkWrapper.runTargetWork(WorkWrapper.java:242)
at oracle.j2ee.connector.work.WorkWrapper.doWork(WorkWrapper.java:215)
at oracle.j2ee.connector.work.WorkWrapper.run(WorkWrapper.java:190)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:825)
at java.lang.Thread.run(Thread.java:595)
Caused by: javax.transaction.RollbackException: Timed out
at com.evermind.server.ApplicationServerTransaction.checkForRollbackOnlyWhileInCommit(ApplicationServerTransaction.java:664)
at com.evermind.server.ApplicationServerTransaction.doCommit(ApplicationServerTransaction.java:273)
at com.evermind.server.ApplicationServerTransaction.commit(ApplicationServerTransaction.java:162)
at com.evermind.server.ApplicationServerTransactionManager.commit(ApplicationServerTransactionManager.java:472)
at oracle.tip.esb.server.common.JTAHelper.commitTransaction(JTAHelper.java:176)
... 6 more
]]></SUPPL_DETAIL>
</PAYLOAD>
</MESSAGE>


Selection failures in the container logfile in $ORACLE_HOME/opmn/logs/deault_group~oc4j_soa~default_group~1.log for BPEL:

10/12/02 07:12:10 com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure}
messageType: {}
parts: {{summary=<summary>empty variable/expression result.
xpath variable/expression expression "/ns1:Fault/ns1:EBMReference/ns1:SenderReference/ns1:ID" is empty at line 100, when attempting reading/copying it.
Please make sure the variable/expression result "/ns1:Fault/ns1:EBMReference/ns1:SenderReference/ns1:ID" is not empty.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain administration page.
</summary>
}}


10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELAssignWMP.evalFromValue(BPELAssignWMP.java:667)
10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELAssignWMP.__executeStatements(BPELAssignWMP.java:143)
10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELActivityWMP.perform(BPELActivityWMP.java:199)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.performActivity(CubeEngine.java:3714)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.handleWorkItem(CubeEngine.java:1657)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.message.instance.PerformMessageHandler.handleLocal(PerformMessageHandler.java:75)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.handleLocalMessage(DispatchHelper.java:220)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.sendMemory(DispatchHelper.java:325)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.endRequest(CubeEngine.java:5787)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.createAndInvoke(CubeEngine.java:1089)
10/12/02 07:12:10 at com.collaxa.cube.engine.delivery.DeliveryService.handleInvoke(DeliveryService.java:646)
10/12/02 07:12:10 at com.collaxa.cube.engine.ejb.impl.CubeDeliveryBean.handleInvoke(CubeDeliveryBean.java:381)
10/12/02 07:12:10 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
10/12/02 07:12:10 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
10/12/02 07:12:10 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
10/12/02 07:12:10 at java.lang.reflect.Method.invoke(Method.java:592)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.joinpoint.EJBJoinPointImpl.invoke(EJBJoinPointImpl.java:35)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.JAASInterceptor$1.run(JAASInterceptor.java:31)
10/12/02 07:12:10 at com.evermind.server.ThreadState.runAs(ThreadState.java:693)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.JAASInterceptor.invoke(JAASInterceptor.java:34)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.TxRequiredInterceptor.invoke(TxRequiredInterceptor.java:50)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.InvocationContextPool.invoke(InvocationContextPool.java:55)
10/12/02 07:12:10 at com.evermind.server.ejb.StatelessSessionEJBObject.OC4J_invokeMethod(StatelessSessionEJBObject.java:87)
10/12/02 07:12:10 at CubeDeliveryBean_LocalProxy_4bin6i8.handleInvoke(Unknown Source)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.message.invoke.InvokeInstanceMessageHandler.handle(InvokeInstanceMessageHandler.java:37)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.handleMessage(DispatchHelper.java:141)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.BaseDispatchTask.run(BaseDispatchTask.java:58)
10/12/02 07:12:10 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
10/12/02 07:12:10 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
10/12/02 07:12:10 at java.lang.Thread.run(Thread.java:595)
<2010-12-02 07:12:10,638> <ERROR> <default.collaxa.cube.xml> com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure}
messageType: {}
parts: {{summary=<summary>empty variable/expression result.
xpath variable/expression expression "/ns1:Fault/ns1:EBMReference/ns1:SenderReference/ns1:ID" is empty at line 100, when attempting reading/copying it.
Please make sure the variable/expression result "/ns1:Fault/ns1:EBMReference/ns1:SenderReference/ns1:ID" is not empty.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain administration page.
</summary>
}}


10/12/02 07:12:10 com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure}
messageType: {}
parts: {{summary=<summary>empty variable/expression result.
xpath variable/expression expression "/ns1:Fault/ns1:EBMReference/ns1:BusinessScopeReference/ns1:InstanceID" is empty at line 118, when attempting reading/copying it.
Please make sure the variable/expression result "/ns1:Fault/ns1:EBMReference/ns1:BusinessScopeReference/ns1:InstanceID" is not empty.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain administration page.
</summary>
}}


10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELAssignWMP.evalFromValue(BPELAssignWMP.java:667)
10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELAssignWMP.__executeStatements(BPELAssignWMP.java:143)
10/12/02 07:12:10 at com.collaxa.cube.engine.ext.wmp.BPELActivityWMP.perform(BPELActivityWMP.java:199)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.performActivity(CubeEngine.java:3714)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.handleWorkItem(CubeEngine.java:1657)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.message.instance.PerformMessageHandler.handleLocal(PerformMessageHandler.java:75)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.handleLocalMessage(DispatchHelper.java:220)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.sendMemory(DispatchHelper.java:325)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.endRequest(CubeEngine.java:5787)
10/12/02 07:12:10 at com.collaxa.cube.engine.CubeEngine.createAndInvoke(CubeEngine.java:1089)
10/12/02 07:12:10 at com.collaxa.cube.engine.delivery.DeliveryService.handleInvoke(DeliveryService.java:646)
10/12/02 07:12:10 at com.collaxa.cube.engine.ejb.impl.CubeDeliveryBean.handleInvoke(CubeDeliveryBean.java:381)
10/12/02 07:12:10 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
10/12/02 07:12:10 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
10/12/02 07:12:10 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
10/12/02 07:12:10 at java.lang.reflect.Method.invoke(Method.java:592)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.joinpoint.EJBJoinPointImpl.invoke(EJBJoinPointImpl.java:35)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.JAASInterceptor$1.run(JAASInterceptor.java:31)
10/12/02 07:12:10 at com.evermind.server.ThreadState.runAs(ThreadState.java:693)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.JAASInterceptor.invoke(JAASInterceptor.java:34)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.TxRequiredInterceptor.invoke(TxRequiredInterceptor.java:50)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.system.DMSInterceptor.invoke(DMSInterceptor.java:52)
10/12/02 07:12:10 at com.evermind.server.ejb.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:119)
10/12/02 07:12:10 at com.evermind.server.ejb.InvocationContextPool.invoke(InvocationContextPool.java:55)
10/12/02 07:12:10 at com.evermind.server.ejb.StatelessSessionEJBObject.OC4J_invokeMethod(StatelessSessionEJBObject.java:87)
10/12/02 07:12:10 at CubeDeliveryBean_LocalProxy_4bin6i8.handleInvoke(Unknown Source)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.message.invoke.InvokeInstanceMessageHandler.handle(InvokeInstanceMessageHandler.java:37)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.DispatchHelper.handleMessage(DispatchHelper.java:141)
10/12/02 07:12:10 at com.collaxa.cube.engine.dispatch.BaseDispatchTask.run(BaseDispatchTask.java:58)
10/12/02 07:12:10 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
10/12/02 07:12:10 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
10/12/02 07:12:10 at java.lang.Thread.run(Thread.java:595)
<2010-12-02 07:12:10,643> <ERROR> <default.collaxa.cube.xml> com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure}
messageType: {}
parts: {{summary=<summary>empty variable/expression result.
xpath variable/expression expression "/ns1:Fault/ns1:EBMReference/ns1:BusinessScopeReference/ns1:InstanceID" is empty at line 118, when attempting reading/copying it.
Please make sure the variable/expression result "/ns1:Fault/ns1:EBMReference/ns1:BusinessScopeReference/ns1:InstanceID" is not empty.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain administration page.
</summary>
}}

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