Systems Engineering and RDBMS

Sequence Enhancement in Oracle 11g

Posted by decipherinfosys on February 9, 2008

Starting with Oracle 11g, we can use sequences with straight variable assignment. Before 11g, we always have to use SELECT INTO clause to get sequence value in the variable. It is not a major change but just makes it easier to use. Let us see it with an example. We will first create a sequence.

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1;

In Following PL/SQL block, we will show both ways (In 10g and prior, 11g) to obtain the next value of the sequence. You need to issue ‘set serveroutput on’ to display the output values.

DECLARE

V_10gVal Number := 0;
V_11gVal Number := 0;

BEGIN

– Oracle 10g and prior
SELECT TEST_SEQ.NEXTVAL
INTO v_10gval
FROM DUAL;

Dbms_output.put_line(’10g Sequence Value = ‘ || v_10gval);

– Oracle 11g
V_11gval := TEST_SEQ.NEXTVAL;

Dbms_output.put_line(’11g Sequence Value = ‘ || v_11gval);

END;
/

Here is the output.

10g Sequence Value = 1
11g Sequence Value = 2

We can also use nextval assignment into INSERT and UPDATE statement. We can use it even in the SELECT statement to generate running numbers.

SQL> SELECT TEST_SEQ.NEXTVAL,table_name
2 FROM user_tables;

NEXTVAL TABLE_NAME
———- ——————————
1 DEPT
2 EMP
3 BONUS
4 SALGRADE
5 TEST_COMPUTE
6 TEST

One thing to remember is that sequence number is generated only once for each row. If same sequence is used for the same row, it will generate the same sequence number for a given row.

SQL> SELECT TEST_SEQ.NEXTVAL,table_name, TEST_SEQ.NEXTVAL AS ROW_NUM
2 FROM user_tables;

NEXTVAL TABLE_NAME ROW_NUM
———- —————————— ———-
7 DEPT 7
8 EMP 8
9 BONUS 9
10 SALGRADE 10
11 TEST_COMPUTE 11
12 TEST 12

CURRVAL is another pseudo column apart from NEXTVAL which can be used with sequence. But CURRVAL cannot be used unless NEXTVAL is used first for the sequence. If it is used then Oracle will give you an error. Here is an example. This is valid for all versions of Oracle.

– Create sequence first.
CREATE SEQUENCE NEW_SEQ;

– Execute following PL/SQL block.
DECLARE

V_ID1 NUMBER;
V_ID2 NUMBER;

BEGIN

V_ID1 := NEW_SEQ.CURRVAL;

EXCEPTION
WHEN OTHERS THEN
Dbms_output.put_line(SQLERRM);
— We will obtain nextval first.
V_ID1 := NEW_SEQ.NEXTVAL;
Dbms_output.put_line(‘Next Value = ‘ || v_ID1);
V_ID2 := NEW_SEQ.CURRVAL;
Dbms_output.put_line(‘Current Value = ‘ || v_ID2);
END;
/

Here is the output.

ORA-08002: sequence NEW_SEQ.CURRVAL is not yet defined in this session
Next Value = 1
Current Value = 1

About these ads

One Response to “Sequence Enhancement in Oracle 11g”

  1. [...] Engineering and RDBMS reports on Sequence Enhancement in Oracle 11g: “Starting with Oracle 11g, we can use sequences with straight variable assignment. Before [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: