SQL DECODE Function in an Adapter Service on IS 6.5

Hi,
I am migrating some adapter components from ES to IS and I have a problem with the DECODE Function.

The on the ES Adapter this is the DECODE function on one of the columns
DECODE(TRIM(${CUST_SUFFIX_IND}),’’,NULL,${CUST_SUFFIX_IND})
I used the upgrade utility to migrate the adapter to IS and, sure enough it created the Adapter Service and for the same column, the expression is
DECODE(TRIM(?),’’,NULL,?).

When I try to run this adapter service I get a “ORA-01008: not all variables bound” error.

Please help!!!

Thank you,
Z

Hi

I got the following details for the oracle error , it says some values have to be populated

ORA-01008: not all variables bound
Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.
Action: In OCI, use an OBIND or OBINDN call to substitute the required values.

Cheers
Jeevan

Hi,
What is OCI? Did you get that error in Developer?
Can you give me a lil bit more information?

Thank you!

Hi

I had not received this kind of error.

It would be great if some one who has faced would chip in.

I had used the OCI ( Oracle Call Interface) which is a type 2 driver which connects to oracle directly and needs a oracle client to connect to the db, unlike thin drivers which are platform independent and 100% java based.

Cheers
Jeevan

Jeevan,

The question marks (?) that you see in your SQL statement are simply place holders (substitution variables) for values that you need to specify. You must bind a variable to each question mark. If you don’t, you will receive that error.

In the JDBC adapter, the way you bind a variable to a ? is by specifying an Input Field for each ? in the SQL. So, in your case, you should specify two Input Fields, it looks like, named custSuffixInd or CUST_SUFFIX_IND, for example. Once you specify an Input Field, it will automatically become part of the Input to that adapter service. Then, during runtime, the question marks will be replaced with values contained in those variables/input fields.

  • Percio