Wednesday, July 9, 2008

Assign sequence value to variable in plsql

To cut the chase, the solution is:
select [sequence] into [variable] from dual;

Update: Oracle 11 allow to assign sequence directly. e.g. [variable] := [sequence].nextval;

Read further to find out my opinion on this syntax.


One thing struck me today while I was preparing a script for data migration project. But before that, let me briefly explain sequence in plsql.

Sequence in plsql is a feature that allow developer to keep track the key count. So, it save you from having a table of storing key value. The basic use of this sequence is simple. For example, I have created a sequence name s_employeeid.

To get the increament value:

To get the current value is:

The syntax for insert a employee record may be look something like this:
Insert into Employee (id, name, age) values (s_employeeid.nextval, 'Alexander', n_age);

Note: n_age is a number viarable.

From the sql statement, it worked rather straight forward. What happened to me today is when I want to assign the sequence to a viarable and in the meantime, I want to pass the sequence to a function. Something like below
n_empid := s_employeeid.currval;
insert_employee(s_employeeid.currval, 'David', n_age);

Both syntax return error. I was surprised and find no reason why both syntax would return error, as sequence and viariable both used exactly the same way. Is the viarable and the sequence has diffrent datatype? Nope, I even defined the variable exactly the same as the sequence.

n_empid s_employeeid%type;

It still won't work after couple of try. Finally it was like this:
select n_employeeid.currval into n_empid from dual;

I only able to pass the sequence's value by using the dual table. Something like a dummy sql.

In conclusion, I don't like it. I thought that a feature that act and behave almost the same as a variable will allow direct variable assignment; but not in plsql. Why?

I rather stop asking.

Update: There are also one mind boggling syntax, which is common on using null in where clause or in if-then-else statement.
Select * from
Employe where name = null;
If = null then
Select * from Employee where name is null;
If is null then

I found null keyword is special and required a "is" word to handle it. :P