Posted by: Lakshmi Venkatesh
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 !!
SELECT dummy_seq.nextval, dummy_seq.currval INTO v_seq_new, v_seq_old FROM dual;
Also, these sequences can be displayed in the trace messages also !!
CREATE OR REPLACE FUNCTION samp_debug (in_number NUMBER) RETURN NUMBER
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);
SQL> set serveroutput on
SQL> SELECT samp_debug(5) from dual;
The value of in_number is 5 sequence is 67562 out number is 337810
v_seq_new := dummy_seq.nextval;
v_seq_old := dummy_seq.currval ;