SQL & PL/SQL

Hello

I'm helping out a developer who has coded a C++ application against Oracle 10g and they are hitting an error they cannot resolve. They are hitting an ORA-01008: not all variables bound error
but do not know why. Can someone please provide some pointers or direction as to where they could try and resolve the problem (or what is actually wrong if obvious)? Please see the following for details / evidence of the problem:

_1. ODBC trace_
The following is the extract from the ODBC trace showing the issue when binding the four parameters to a SQL insert:

[ODBC][8350][SQLPrepare.c][192]
Entry:
Statement = 0x1270d770
SQL = [INSERT INTO pref (priority, sid, external_user_id, ou) VALUES (?,?,?,?)][length = 71 (SQL_NTS)]
[ODBC][8350][SQLPrepare.c][367]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLFreeStmt.c][140]
Entry:
Statement = 0x1270d770
Option = 3
[ODBC][8350][SQLFreeStmt.c][246]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLFreeStmt.c][140]
Entry:
Statement = 0x1270d770
Option = 0
[ODBC][8350][SQLFreeStmt.c][246]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLBindParameter.c][210]
Entry:
Statement = 0x1270d770
Param Number = 1
Param Type = 1
C Type = 4 SQL_C_LONG
SQL Type = 4 SQL_INTEGER
Col Def = 4
Scale = 0
Rgb Value = 0x7fff074b071c
Value Max = 4
StrLen Or Ind = (nil)
[ODBC][8350][SQLBindParameter.c][390]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLBindParameter.c][210]
Entry:
Statement = 0x1270d770
Param Number = 2
Param Type = 1
C Type = 1 SQL_C_CHAR
SQL Type = 1 SQL_CHAR
Col Def = 20
Scale = 0
Rgb Value = 0x126f1b08
Value Max = 20
StrLen Or Ind = (nil)
[ODBC][8350][SQLBindParameter.c][390]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLBindParameter.c][210]
Entry:
Statement = 0x1270d770
Param Number = 3
Param Type = 1
C Type = 1 SQL_C_CHAR
SQL Type = 1 SQL_CHAR
Col Def = 0
Scale = 0
Rgb Value = 0x616c38
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][8350][SQLBindParameter.c][390]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLBindParameter.c][210]
Entry:
Statement = 0x1270d770
Param Number = 4
Param Type = 1
C Type = 1 SQL_C_CHAR
SQL Type = 1 SQL_CHAR
Col Def = 7
Scale = 0
Rgb Value = 0x1270ad28
Value Max = 7
StrLen Or Ind = (nil)
[ODBC][8350][SQLBindParameter.c][390]
Exit:[SQL_SUCCESS]
[ODBC][8350][SQLExecute.c][183]
Entry:
Statement = 0x1270d770
[ODBC][8350][SQLExecute.c][344]
Exit:[SQL_ERROR]
DIAG [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound

*2. SQL Plus Testing*
We've done some testing in SQL Plus and all appears fine when the following is done to try and replicate what the application is doing:

declare variables:
SQL> var myvar_priority number
SQL> var myvar_sid VARCHAR2(128)
SQL> var myvar_userid VARCHAR2(128)
SQL> var myvar_ou VARCHAR2(64)

assign values to variables:
SQL> begin
2 select 1234,'S-2-2-2-2', 'zzz', 'bar' into :myvar_priority,:myvar_sid,:myvar_userid,:myvar_ou from dual;
3 end;
4 /

PL/SQL procedure successfully completed.

Run insert statement:
insert into pref (priority, sid, external_user_id, ou)
values(:myvar_priority,:myvar_sid,:myvar_userid,:myvar_ou);


If there is anything blindingly obvious, please let me know!

Cheers!
Thanks.

Edited by: user5828592 on 17-Mar-2011 09:07