Oracle "ORA-01008" Error. Variable Not Bound

Question

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

where role=role; is making no difference between the table's field role and the variable named role. Do not use the same name for field and variable. Besides, you don't have to use : to reference the variable, just use for example: where role=v_role; — Siberia, Oct 11 '22 at 03:30

score 3 · Accepted Answer · answered Nov 06 '13 at 05:59

3

Try:

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

answered Nov 06 '13 at 05:59

valex

23,966
7
43
60

But why can't I use the same variable name as I have in my table? say `role`. And then what is the difference between `:role_test` and `role_test`.? – Veer Shrivastav Nov 06 '13 at 06:04
@Veer It's not a dynamic SQL. For more details: [SO: Use of bind variable](http://stackoverflow.com/questions/5209981/use-of-bind-variable) – valex Nov 06 '13 at 06:22
@Veer If you use `role=role` with the same name as a field in the table you just compare field with its self not with a variable so it is always true. – valex Nov 06 '13 at 06:24

score 1 · Answer 2 · answered Nov 06 '13 at 06:16

If you want to use the same variable name as your column name, You can try with a procedure like this,

CREATE OR REPLACE PROCEDURE role_p(role VARCHAR2)
IS
BEGIN
     UPDATE veer_test SET project_id=1008422 WHERE role = role_p.role;
END;

To Execute:

EXECUTE role_p('Programmer');

score 1 · Answer 3 · answered Nov 06 '13 at 06:18

You can consult v$reserved_words in order to determine if you can use a specific word such as ROLE in PL/SQL:

select res_attr from v$reserved_words where keyword = 'ROLE';

The fact that this statement returns a row indicates that indeed ROLE is indeed a keyword, and hence cannot be used in PL/SQL. Yet, res_attr_ is N which indicates that the word can be used in a table for an attribute name.

See also v$reserved_words on tahiti.oracle.com

score 0 · Answer 4 · edited Nov 06 '13 at 06:06

0

you try

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

May this work

edited Nov 06 '13 at 06:06

Veer Shrivastav

5,434
11
53
83

answered Nov 06 '13 at 06:03

乌鸦不会飞

327
3
5

But why can't I use the same variable name as I have in my table? say `role`. And then what is the difference between `:role_test` and `role_test`? – Veer Shrivastav Nov 06 '13 at 06:07

Oracle "ORA-01008" Error. Variable Not Bound

4 Answers4