Sql – Oracle “ORA-01008” Error. Variable Not Bound

oracleplsqlsql

I have just started with PL/SQL and writing a simple fundamental code. I am not getting, what is wrong with the code.

DECLARE
role_test varchar(40) := 'Programmer';
BEGIN
update veer_test set project_id=1008420 where role=:role_test;
END;

I am getting ORA-01008: not all variables bound.

Earlier I tried:

DECLARE
role varchar(40) := 'Programmer';
BEGIN
update veer_test set project_id=1008420 where role=role;
END;

This updated all the rows of the table. Well this was justifiable. But what if I want to use the same variable name as in the table. I also tried:

role=:role;

But same error ORA-01008

Best Answer

Try:

DECLARE
role_test varchar(40) := 'Programmer';
BEGIN
update veer_test set project_id=1008420 where role=role_test;
END;

Related Solutions

C# – ORA-01008: not all variables bound. They are bound

I know this is an old question, but it hasn't been correctly addressed, so I'm answering it for others who may run into this problem.

By default Oracle's ODP.net binds variables by position, and treats each position as a new variable.

Treating each copy as a different variable and setting it's value multiple times is a workaround and a pain, as furman87 mentioned, and could lead to bugs, if you are trying to rewrite the query and move things around.

The correct way is to set the BindByName property of OracleCommand to true as below:

var cmd = new OracleCommand(cmdtxt, conn);
cmd.BindByName = true;

You could also create a new class to encapsulate OracleCommand setting the BindByName to true on instantiation, so you don't have to set the value each time. This is discussed in this post

Related Topic