ORA-01008: not all variables bound

, ,
1

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 )

2

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.

3

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?

4

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.