Hi Everyone!

I’ve created a JDBC adapter service with the following custom SQL insert statement and is getting this error (“ORA-01008: not all variables bound”). This statement works fine when i tested in Toad (replacing ? with values). Any help would be greatly appreciate it.

INSERT INTO TEST_TABLE (
TEST_ID, PARTNER_ID, PARTY_ID,
ADDRESS1, ADDRESS2, ADDRESS3,
CITY, REGION, ZIP_CODE, COUNTRY, RECEIVE_DT )
VALUES ( ?, ?, ?,
DECODE(GREATEST(LENGTH(?),50),50,?,SUBSTR(?,1,50)),
DECODE(GREATEST(LENGTH(?),50),50,?,SUBSTR(?,1,50)),
DECODE(GREATEST(LENGTH(?),50),50,?,SUBSTR(?,1,50)),
?, ?, ?, ?, SYSDATE )

Be sure you are providing a value for each of the sixteen (16) bind variables (“?” - question marks) in your SQL statement. You may be forgetting that there are 3 bind variables in each of the DECODE functions for the address fields in your statement.

Allen.

Thanks Allen. You’re right, i needed to add the 3 binding variables in each of the DECODE functions for the address fields in my statement. That fixed the “ORA-01008: not all variables bound” error. But now, i’m getting “ORA-01401: inserted value too large for column”. Not sure why the error, since the functions in the statement should truncate the values. Any idea?

Okay, got it to work. Needed to remove TEST_ID from sql statement and remove RECEIVE_DT from the input. Then it works fine. Thanks again.