Oracle Sequence

Sequence is an Oracle database object which uses to generate unique integers.

Syntax :-

create sequence seq_empno
start with 10
increment by 1
maxvalue 100000
minvalue 1;

The above will create a sequence with name seq_empno starting with integer 10 increment by 1 and max value as 100000.


It is used to increment the sequence value.

Select seq_empno.nextval from dual;


It is used to find the current value of the sequence.

Select seq_empno.currval from dual;

Note :- If sequence.nextval is not set, sequence.currval cannot be executed.

Connected to:
Oracle Database 11g Enterprise Edition Release – 64-bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select seq_empno.currval from dual;
select seq_empno.currval from dual
ERROR at line 1:
ORA-08002: sequence SEQ_EMPNO.CURRVAL is not yet defined in this session

Oracle 11g version onwards sequence can be directly accessed from PL/SQL. This is a new feature in Oracle 11g.

SQL> declare
2   n integer;
3  begin
4    n := seq_empno.nextval;
5    dbms_output.put_line(‘value of seq =’ || n);
6  end;
7  /

value of seq =4

PL/SQL procedure successfully completed.

So far oracle sequences are the best way to generate unique integers like primary key columns. In multi-user environments each user gets different sequence number always.

I would say sequences has some draw backs too.

1) Majority of the corporate environments and development centers we will have at least 3 different set of databases. One for development, one for QA purpose and other for obviously production. When we use sequence for primary columns the numbers would be ideally different for these three environments. This cause lot of confusion.

for example consider below

Empno Ename Empno Ename Empno Ename
10 John 11 John 12 John
12 Clerk 12 Clerk 13 Clerk
14 Change 14 Change 14 Change

Empno is not same in different environments

2) Other issue arises when we do database refresh, say production data to QA database. This process will over write the sequence number columns too.

3) Skipped sequences are another one.

Consider below example of inserting 10 records into table and primary key is associated with sequence.

SQL> create table salary(sal_no number primary key ,sal number, delete_ind varchar2(1));

Table created.

SQL> create sequence seq_sal_no
2  start with 1
3  increment by 1
4  maxvalue 1000
5  minvalue 1;

Sequence created.

SQL> select seq_sal_no.nextval from dual;


SQL> select seq_sal_no.currval from dual;


SQL> begin
2  insert into salary values(seq_sal_no.nextval,1000,’N’);
3  insert into salary values(seq_sal_no.nextval,2000,’N’);
4  insert into salary values(seq_sal_no.nextval,3000,’NN’); — This will raise error
5  commit;
6  exception
7     when others then
8         dbms_output.put_line(‘Error is ‘ || sqlerrm);
9         rollback;
10  end;
11  /
Error is ORA-12899: value too large for column “MANOJ”.”SALARY”.”DELETE_IND”
(actual: 2, maximum: 1)

PL/SQL procedure successfully completed.

SQL> select seq_sal_no.currval from dual;


The PL/SQL raises error and rolled back. But sequence got incremented. Though it is the Oracle behavior it creates a lot of hassle.