I discovered today that in Oracle 10g (and I presume in earlier versions also) it is not possible to directly assign a sequence value in to a PLSQL variable, hence neither of the 2 following examples will compile in 10g:
1: Try
PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
-- Doesn't compile
nice_unique_number NUMBER := mysequence.nextval;
BEGIN
-- do stuff here...
END set_up_test_data;
IS
-- Doesn't compile
nice_unique_number NUMBER := mysequence.nextval;
BEGIN
-- do stuff here...
END set_up_test_data;
2: Try
PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
nice_unique_number NUMBER;
BEGIN
-- Doesn't compile.
nice_unique_number := mysequence.nextval;
-- do stuff here...
END set_up_test_data;
IS
nice_unique_number NUMBER;
BEGIN
-- Doesn't compile.
nice_unique_number := mysequence.nextval;
-- do stuff here...
END set_up_test_data;
A bit of googling suggests that a common workaround to this is to be do a SELECT INTO using the dual table, as below:
PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
nice_unique_number NUMBER;
BEGIN
--Works nicely
SELECT mysequence.nextval INTO nice_unique_number FROM dual;
-- do stuff here...
IS
nice_unique_number NUMBER;
BEGIN
--Works nicely
SELECT mysequence.nextval INTO nice_unique_number FROM dual;
-- do stuff here...
e.g. INSERT INTO TABLE_1 (COLUMN1, COLUMN2, COLUMN3) VALUES (nice_unique_number, value.table_1.column1, value_2.table_1.column2);
END set_up_test_data;
END set_up_test_data;
Note: This no appears to be an issue as of Oracle 11g (search for Straight Sequences).
source: http://www.eddgrant.com/blog/EdD/entry/assigning_a_sequence_value_to
