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
– Siberia, Commented Oct 11, 2022 at 3:30

valex · Accepted Answer · 2013-11-06 05:59:51Z

3

Try:

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

answered Nov 6, 2013 at 5:59

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Veer Shrivastav Over a year ago

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

valex Over a year ago

@Veer It's not a dynamic SQL. For more details: SO: Use of bind variable

valex Over a year ago

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

Dba · Accepted Answer · 2013-11-06 06:16:08Z

1

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');

answered Nov 6, 2013 at 6:16

Dba

6,6791 gold badge26 silver badges33 bronze badges

Comments

René Nyffenegger · Accepted Answer · 2013-11-06 06:18:43Z

1

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

answered Nov 6, 2013 at 6:18

René Nyffenegger

40.9k34 gold badges180 silver badges321 bronze badges

Comments

Veer Shrivastav · Accepted Answer · 2013-11-06 06:06:45Z

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 6, 2013 at 6:06

Veer Shrivastav

5,49611 gold badges58 silver badges84 bronze badges

answered Nov 6, 2013 at 6:03

乌鸦不会飞

3373 silver badges5 bronze badges

1 Comment

Veer Shrivastav Over a year ago

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?

Collectives™ on Stack Overflow

Oracle "ORA-01008" Error. Variable Not Bound

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Related