Author: Greg Ryhon

Page: 1 2 3

Oracle Sequences:

Oracle sequences can be used to provide a sequential series of numbers by eliminating the overhead of disk I/O and transaction locking. Oracle sequences are often used for primary and unique keys.

Sequences prior to Oracle 11g:

In prior releases of Oracle one could refer to sequence values in SQL statements by using pseudocolumns CURRVAL and NEXTVAL .

However prior releases of Oracle does not allow us to use pseudocolumns directly in PL/SQL and we always have to use SELECT INTO clause to get sequence value in the variable.

Sequence enhancements in Oracle 11g:

Oracle 11g has enhanced the sequence feature and enables us to use the pseudocolumns CURRVAL and NEXTVAL in a PL/SQL expression. Now you can use sequences with straight variable assignment. Although it is not a major change but makes it easier to use. Let us see an example

Create Sequence:

First of all we will create a sequence.

CREATE SEQUENCE MYSEQUENCE INCREMENT BY 1;

Declare Variables:

Now we will show how to obtain next value of sequence in prior releases of Oracle and in enhanced Oracle 11g sequence feature. First we will declare TESTSEQUENCE_VAR_10G for obtaining next value of sequence in Oracle releases prior to 11g and TESTSEQUENCE_VAR_11G obtaining value in Oracle 11g. The ‘set serveroutput on' command will be used to display the output values.

DECLARE

TESTSEQUENCE_VAR_10G Number := 0;
TESTSEQUENCE_VAR_11G Number := 0;

Prior to 11g: Getting sequence value in variable

We will use SELECT INTO clause to get sequence value in the variable.

BEGIN

SELECT MYSEQUENCE.NEXTVAL
INTO TESTSEQUENCE_VAR_10G
FROM DUAL;

END;
/

Dbms_output.put_line('The value of 10g sequence = ‘ || TESTSEQUENCE_VAR_10G);

Oracle 11g: Getting sequence value in variable

In Oracle 11g we will directly assign variable value.

BEGIN

TESTSEQUENCE_VAR_11G := MYSEQUENCE.NEXTVAL;

Dbms_output.put_line('The value of 11g sequence = ‘ || TESTSEQUENCE_VAR_11G);

END;
/

Output:

The value of 10g sequence = 1
The value of 11g sequence = 2 Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Single-Set Aggregates for DML Statements!!
   A Few Things To Know Regarding Databases
   DBA Tips for Oracle Tablespaces!!
   Why do we need a good database design?