When using the DbAdapter with an IBM DB2 database you encounter: java.sql.SQLException: [SQL0104] Token XXXXX was not valid. Valid tokens: DECLARE. (Doc ID 1270881.1)

Last updated on OCTOBER 05, 2011

Applies to:

Oracle SOA Platform - Version: 11.1.1.2.0 and later   [Release: 11gR1 and later ]
Information in this document applies to any platform.

Symptoms

You are using the Database Adapter within Oracle Fusion Middleware 11g Release 1 to access a simple stored procedure that is deployed to a DB2 database, for example:

CREATE PROCEDURE test8( IN new_value varchar(40))
LANGUAGE SQL

BEGIN
  UPDATE nameval
  SET value = new_value
  WHERE id = 3;
END
/

You are able to call the procedure successfully from a DB2 SQL client. However, at run-time when a deployed composite invokes the same procedure via the DBAdapter, the composite invocation fails with the following fault:

oracle.fabric.common.FabricInvocationException: BINDING.JCA-12563
Exception occured when binding was invoked.
Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'proc' failed due to: Stored procedure invocation error.
Error while trying to prepare and execute the MY_USER.TEST8 API.
An error occurred while preparing and executing the MY_USER.TEST8. Cause: java.sql.SQLException: [SQL0104] Token MY_USER was not valid. Valid tokens: DECLARE.
".
The invoked JCA adapter raised a resource exception.
Please examine the above error message carefully to determine a resolution.

at oracle.integration.platform.blocks.adapter.fw.jca.cci.EndpointInteractionException.getFabricInvocationException(EndpointInteractionException.java:75)
at oracle.integration.platform.blocks.adapter.AdapterReference.getFabricInvocationException(AdapterReference.java:286)
at oracle.integration.platform.blocks.adapter.AdapterReference.post(AdapterReference.java:272)
at oracle.integration.platform.blocks.mesh.AsynchronousMessageHandler.doPost(AsynchronousMessageHandler.java:142)
at oracle.integration.platform.blocks.mesh.MessageRouter.post(MessageRouter.java:194)
at oracle.integration.platform.blocks.mesh.MeshImpl.post(MeshImpl.java:204)



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