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;
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
.