How do you assign a sequence value to a variable?

Question

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?

Answer 1

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.