Using PL/SQL variables in OCI results in "ORA-01008: not all variables bound" error

Question

The following (highly contrived and simplified) example runs fine in SQLDeveloper, but results in an ORA-01008 error when run through OCI.

declare
  CURRENT_LINE_ID NUMBER := 120;
  TARGETVAR NUMBER;
begin
  SELECT 1 INTO TARGETVAR FROM DUAL WHERE 120 = :CURRENT_LINE_ID;
end;

Is there any way to restructure this so that the bind variable is satisfied in OCI?

I experimented with substitution variables a little (again works in SQL Developer), but DEFINE appear to be completely invalid in OCI.

DEFINE MYSUBST = 120;
DECLARE
  TARGETVAR NUMBER;
BEGIN
  SELECT 1 INTO TARGETVAR FROM DUAL WHERE 120 = &MYSUBST;
END;

Solution

When you use :CURRENT_LINE_ID NUMBER , OCI looks for that bind variable in your host program only. Here C++. So you should have had this variable declare in your c++ program in a exec declare section or wherever it should be. When you run anything in SQL developer, when encountered a :variable, it blindly prompts the user to enter the value for it, so dont mix it up with the way it do and the oci libraries work.

In your case finally, when a PL/SQL is used and variable is declared there, you can always refer it without colon. If you want to bind it from the hostprogram, you have declare it as host variable. PRO*C supports that. not sure about c++. pro*c is nothing but a embedded sql in C provided by oracle.

Comments

[Raven]

SQL Developer does not blindly prompt for completion of :variable if you have provided a value for it in your script already.

[Maheswaran Ravisankar]

Yes! Agreed, if we run the PL/SQL as a script (or F5), after setting the variable.