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.

sequence.nextval

It is used to increment the sequence value.

Select seq_empno.nextval from dual;

sequence.currval

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 11.2.0.1.0 – 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

DEV QA PROD
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>
SQL> create sequence seq_sal_no
2  start with 1
3  increment by 1
4  maxvalue 1000
5  minvalue 1;

Sequence created.

SQL>
SQL> select seq_sal_no.nextval from dual;

NEXTVAL
———-
1

SQL> select seq_sal_no.currval from dual;

CURRVAL
———-
1

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>
SQL> select seq_sal_no.currval from dual;

CURRVAL
———-
5

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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s