SQL 'SELECT IN' clause is not working in the DB Adapter (Doc ID 1559541.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle Service Bus - Version 11.1.1.6.0 to 11.1.1.7.0 [Release 11g]
Oracle SOA Suite - Version 11.1.1.6.0 to 11.1.1.7.0 [Release 11gR1]
Oracle Service Bus - Version 12.1.3.0.0 to 12.1.3.0.0
Information in this document applies to any platform.

Symptoms

On : 11.1.1.6.0 version, Technology Adapters

SQL 'SELECT IN' Clause Not working in DB adapter

When trying to execute a SQL, using the IN clause, in db adapters by using the 'Execute costume SQL' option, an exception occurs.

For example using the following SQL

"select account_no,color_no from SW2000 where ACCOUNT_NO in (?)" fails when passing, for example, 670423904,675251177 as parameters


ERROR
-----------------------
Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'dbMultipleAcc' failed due to: Pure SQL Exception. Pure SQL Execute of select account_no,color_no from SW2000 where ACCOUNT_NO in (?) failed. Caused by org.netezza.error.NzSQLException: No results were returned by the query.. The Pure SQL option is for border use cases only and provides simple yet minimal functionality. Possibly try the "Perform an operation on a table" option instead. ConnectionFactory property platformClassName was set to org.eclipse.persistence.platform.database.oracle.Oracle10Platform but the database you are connecting to is Netezza NPS. Please validate your platformClassName setting. This mismatch can cause the adapter to trigger runtime exceptions or execute SQL that is invalid for the database you are connected to. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-1100" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.

In this case customer used a Netezza database but this applies to any of the supported Databases

 

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a simple project with a DbAdapter performing "Execute costume SQL" option.
2. use a select in clause sql
3. pass as parameters (not hard coded) the coma separated list


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