Today when checking “Search Keyphrases” for my site, I found out that someone wants to know about ‘can we use sequence in plsql’. So I though of explaining it so that in future it will be helpful to others.

Technically you use sequence in PL/SQL only. But I understand what exactly this friend of ours want to ask, he mean can we directly assign value from sequence without using sql.

Let me expain it with example:

DECLARE
l_myvar NUMBER;
BEGIN
/*Method 1 :Assiging using SQL*/
SELECT my_sequence.nextval
INTO l_myvar
FROM dual;

/*Method 2 Assigning it directly without using SQL*/
l_myvar := my_sequence.nextval;
……

END;

Currently only “Method 1:Assigning through SQL” is valid method, you cannot use “Method 2 Assigning it directly without using SQL”

But in future version of Oracle, 11g or whatever next version, Oracle is planning to provide such feature refer my old blog entry Enhancements in Oracle 11g PL/SQL.