Cannot execute the SQL statement. Cursor is closed Error

, ,
1

Hi,
While executing a stored procedure using JDBC adapter 6.5 “ocassionally” the adapter service fails with the below error.
Error Thrown:
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service SP_JDBC_Adapter_Services:callGET_QTY_DETAILS.
[ADA.1.316] Cannot execute the SQL statement “{call APPS.ORDER_PE_PKG.GET_QTY_DETAILS (?, ?, ?, ?, ?, ?, ?, ?) }”. "
(0) Cursor is closed."
Cursor is closed.
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:125)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:116)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceException.java:103)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode.java:388)
at com.wm.pkg.art.ns.ARTNSService.baseInvoke(ARTNSService.java:54)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java:631)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(ReservationProcessor.java:40)
at com.wm.app.b2b.server.invoke.StatisticsProcessor.process(StatisticsProcessor.java:44)
at com.wm.app.b2b.server.invoke.ServiceCompletionImpl.process(ServiceCompletionImpl.java:253)
at com.wm.app.b2b.server.invoke.ValidateProcessor.process(ValidateProcessor.java:51)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:228)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:30)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:636)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:536)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:381)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:237)
at com.wm.app.b2b.server.BaseService.invoke(BaseService.java:189)
at com.wm.lang.flow.FlowInvoke.invoke(FlowInvoke.java:324)
at com.wm.lang.flow.FlowState.invokeNode(FlowState.java:581)
at com.wm.lang.flow.FlowState.step(FlowState.java:441)
at com.wm.lang.flow.FlowState.invoke(FlowState.java:406)
at com.wm.app.b2b.server.FlowSvcImpl.baseInvoke(FlowSvcImpl.java:1040)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java:631)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(ReservationProcessor.java:40)
at com.wm.app.b2b.server.invoke.StatisticsProcessor.process(StatisticsProcessor.java:44)
at com.wm.app.b2b.server.invoke.ServiceCompletionImpl.process(ServiceCompletionImpl.java:253)
at com.wm.app.b2b.server.invoke.ValidateProcessor.process(ValidateProcessor.java:51)
at com.wm.app.b2b.server.ACLManager.process(ACLManager.java:228)
at com.wm.app.b2b.server.invoke.DispatchProcessor.process(DispatchProcessor.java:30)
at com.wm.app.b2b.server.AuditLogManager.process(AuditLogManager.java:636)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:536)
at com.wm.app.b2b.server.invoke.InvokeManager.invoke(InvokeManager.java:381)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:237)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java:119)
at com.wm.app.b2b.server.HTTPWebServicesHandler._process(HTTPWebServicesHandler.java:1349)
at com.wm.app.b2b.server.HTTPWebServicesHandler.process(HTTPWebServicesHandler.java:335)
at com.wm.app.b2b.server.HTTPDispatch.handleRequest(HTTPDispatch.java:185)
at com.wm.app.b2b.server.Dispatch.run(Dispatch.java:333)
at com.wm.util.pool.PooledThread.run(PooledThread.java:118)
at java.lang.Thread.run(Thread.java:595)

Hope the below details will be helpful in analyzing the issue:
wM IS: 7.1.3
JDBC adapter version: 6.5
Adapter fixes applied: WmJDBCAdapter_6.5_Fix26
No other fixes applied for either IS or adapter.
Jar file used for Oracle DB connection: ojdbc14.jar
The Above Jar File size: 1533 KB
DB Details on which the stored procedure lies:
DB Name:Oracle
Version:10g
JDBC adapter connection properties used:
Transation Type: LOCAL_TRANSACTION
networkprotocol: tcp
Other Properties: oracle.jdbc.pool.OracleDataSource
Connection pooling is enabled with minimum pool size set to zero.
Please help.
Thanks in anticipation,
Ravi

2

Hi, Ravi!

I’ve got the same problem. Have you found the solution for it?

Kind regards
Dmitry

3

Share your IS version and JDBC Adapter version with fix levels.

4

Also can I have a look at the SP please?

Did you try executing the same SP on your database? Any errors reported?

5

Hi,

wM IS: 7.1.2

Description JDBC Adapter
Adapter Version 6.5 Release Notes
Updates JDBC_6.5_Fix24
JDBC_6.5_Fix26
WmJDBCAdapter_6.5_Fix29
JDBC_6.5_Fix31
JDBC_6.5_Fix35
JCA Spec Version 1.0

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Many thanks
in advance!

6

What is SP? :oops:
The Server-log:
[9174]Cursor is closed.
9173 Cursor is closed."
[9172][ADA.1.316] Cannot execute the SQL statement “call QUEUEPROCESSING3( ?, ?, ?)”. "
[9171]2015-10-14 10:25:58 MEST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service):
Unable to invoke adapter service SMBBO_Internal.Queuing.jdbc:invoke_plsql_QUEUEPROCESSING3.
[9170]Cursor is closed.
9169 Cursor is closed."
[9168]2015-10-14 10:25:58 MEST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement
“call QUEUEPROCESSING3( ?, ?, ?)”. "

And it happens rather “ocassionally” as well.

7

SP=Stored Procedure

8

OK. Please s. attachment
SP.txt (2.24 KB)

9

Thanks, trying to understand your SP.

Did you execute the same SP directly on Oracle , do face any issues there?

10

Yes, it works smoothly. But 1 call from about 1000 throws the exception. The workaround is that I wrapped the call in the REPEAT step and the second call is always OK. But I still have the error message in the Server log from time to time. The sizing is about 100000 calls / day

11

Can you update the fix levels of JDBC Adapter and see if it fixes the current issue. Current latest fix available is JDBC_6.5_Fix51 - 4/30/2015

12

OK. I’ll try. Thank you very much!

13

Hi Dmitry,

what is the driver version you are using?

If it is Oracle 10g driver, please upgrade Oracle 11g or Oracle 12c as there might be some compatibility issues between these versions.

Regards,
Holger

14

Hi Holger!

Your hint has helped. Thank you!
SOLUTION:

  1. Check the path to ojdbc6.jar in Integration Server Administrator (Server > About)
  2. Download via WinSCP the ojdbc6.jar from the Integration Server and unzip it.
  3. In ojdbc6\META-INF find the file MANIFEST.MF
  4. Check there the Implementation-Version
  5. The bug was fixed in the version 11.2.0.3.0

Bug Description
======= ============================================
…
10314983 CURSOR IS CLOSED IS THROWN FROM JDBC WHEN REF_CURSOR IS NOTAVAILABLE
…

see:
http://download.oracle.com/otn/utilities_drivers/jdbc/11203/Readme.txt

  1. Download the version 11.2.0.3.0+ of ojdbc6.jar from the Internet and put it into the same catalog.
    e.g. Database 19c and 21c | Oracle
  2. Restart the Integration Server

That’s all