Friday, May 7, 2010

Oracle sequence access method in 11g

Before Oracle 11g we could access sequence's current and next value using below method:
select sequence_name.CURRVAL from dual;

However, starting with 11g we can access it directly using seqence name.

Lets have look at example:

In 10G:
SQL> create sequence myseq1 start with 1 increment by 1;

Sequence created.

SQL> declare
2  n1 number;
3  begin
4  n1:=myseq1.nextval;
5  dbms_output.put_line(n1);
6  end;
7  /
n1:=myseq1.nextval;
*
ERROR at line 4:
ORA-06550: line 4, column 12:
PLS-00357: Table,View Or Sequence reference 'MYSEQ1.NEXTVAL' not allowed in
this context
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
As you see, such access method (direct access) is not allowed even in 10g.

In 11g:
SQL> create sequence myseq1 start with 1 increment by 1;

Sequence created.


SQL> declare
2  n1 number;
3  begin
4  n1:=myseq1.nextval;
5  dbms_output.put_line(n1);
6  end;
7  /
1

PL/SQL procedure successfully completed.

Or:

SQL> exec dbms_output.put_line(myseq1.currval);
2

No comments: