Oracle Illustrated

Feb 21 2010   8:37AM GMT

Migrating from 9i to 11g – Sequences

Lakshmi Venkatesh Profile: 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 !!

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: