Migrating from 9i to 11g – Sequences

Prior to Oracle 11g – sequence can be accessed only from a SQL statement. From now on references to sequence is allowed without SQL statement. Oracle recomends to use this method instead of using it in the query. – Time saving !!

Oracle 9i

DECLARE
v_seq_new NUMBER;
v_seq_old NUMBER;
BEGIN
SELECT dummy_seq.nextval, dummy_seq.currval INTO v_seq_new, v_seq_old FROM dual;
END;
/

Elapsed: 00:00:00.57

Oracle 11g
Also, these sequences can be displayed in the trace messages also !!

CREATE OR REPLACE FUNCTION samp_debug (in_number NUMBER) RETURN NUMBER
AS
out_number NUMBER;
BEGIN
out_number := dummy_seq.nextval * in_number;
dbms_output.put_line(‘The value of in_number is ‘ || in_number || ‘ sequence is ‘ || dummy_seq.currval || ‘ out number is ‘ || out_number);
return out_number;
END samp_debug;
/

Test :

SQL> set serveroutput on
SQL> SELECT samp_debug(5) from dual;

SAMP_DEBUG(5)
————-
337810

The value of in_number is 5 sequence is 67562 out number is 337810

DECLARE
v_seq_new NUMBER;
v_seq_old NUMBER;
BEGIN
v_seq_new := dummy_seq.nextval;
v_seq_old := dummy_seq.currval ;
END;
/

Elapsed: 00:00:00.06