Home > oracle_11g_new_features > 11g Enhancement in oracle sequence fetching

11g Enhancement in oracle sequence fetching

In 10g we have created sequence called ora10g and we use e a SELECT..INTO..FROM DUAL construct.

DECLARE
i PLS_INTEGER;
BEGIN
i := ora10g.NEXTVAL;
DBMS_OUTPUT.PUT_LINE(‘Printing NextVal =’ || TO_CHAR(i));
END;
/

Error :

ORA-06550: line 4, column 11:
PLS-00201: identifier ‘ORA10G.NEXTVAL’ must be declared
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored

Correct usage :

DECLARE
i PLS_INTEGER;
BEGIN
/* i := ora10g.NEXTVAL; */
SELECT ukatru.ora10g.NEXTVAL AS snval INTO i FROM dual;
DBMS_OUTPUT.PUT_LINE(‘Printing Nextval=’ || TO_CHAR(i));
END;
/

Printing Nextval=2

==========================================================

oracle 11g  has added sequence fetching (using NEXTVAL and CURRVAL) to the list of valid PL/SQL expressions. This means we can use sequence expressions in PL/SQL anywhere we can validly use a number.

DECLARE
i PLS_INTEGER;
BEGIN
i := ora11g.NEXTVAL;
DBMS_OUTPUT.PUT_LINE(‘Printing Nextval in 11g environment=’ || TO_CHAR(i));
END;
/

Printing Nextval in 11g environment=1

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: