CODEWITHSUNDEEP
sqloracle-database
Nick Brunt
Nick Brunt

Reputation: 10047

How do you assign a sequence value to a variable?

I need to assign a sequence value to a variable for use later after the sequence value has been incremented. I've tried this but it gives an error:

variable imageID number;
select SEQ_IMAGE_ID.CURRVAL into :imageID from dual;

select * from IMAGES where IMAGE_ID = :imageID;


Error starting at line 2 in command:
select SEQ_IMAGE_ID.CURRVAL into :imageID from dual
Error report:
SQL Error: ORA-01006: bind variable does not exist
01006. 00000 -  "bind variable does not exist"

I have triple checked that the sequence name is correct, any ideas?

Upvotes: 6

Views: 16987

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

You seem to be doing this in SQL*Plus or SQL Developer, from the variable declaration. You need to do the assignment in a PL/SQL block, either with an explicit begin/end or with the exec call that hides that:

variable imageID number;
exec select SEQ_IMAGE_ID.CURRVAL into :imageID from dual;
select * from IMAGES where IMAGE_ID = :imageID;

If you're using 11g you don't need to select, you can just assign:

variable imageID number;
exec :image_id := SEQ_IMAGE_ID.CURRVAL;
select * from IMAGES where IMAGE_ID = :imageID;

You could also use a substitution variable:

column tmp_imageid new_value image_id;
select SEQ_IMAGE_ID.CURRVAL as tmp_imageID from dual;
select * from IMAGES where IMAGE_ID = &imageID;

Note the change from : to indicate a bind variable, to & to indicate a substitution variable.

Upvotes: 8

GolezTrol
GolezTrol

Reputation: 116100

In PL/SQL, the variable needs to be declared, something like this:

declare
  V_IMAGEID;
begin
  select SEQ_IMAGE_ID.CURRVAL into V_IMAGEID from dual;

  select * /*into ... */ from IMAGES where IMAGE_ID = V_IMAGEID;
end;

If you're using bind variables, the variable must be bound. The error message indicates this isn't the case. How exactly to bind variables depends on the language/situation. Make sure you use the right direction when binding variables. In the first (dual) query, you will need an out parameter. You may need to specify this.

Upvotes: 2

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

just remove ':' before :imageId. If you are in a trigger use :new.imageid

the word variable should be removed as well

p.s. I mean anonymous block surely.

Upvotes: 1

Related Questions