Oracle 11g PL/SQL: Allow Sequences in PL/SQL Expressions


Yet Another Optimization or a Compiler Trick?

In Oracle 11g, Oracle finally allows PL/SQL developers to use sequences in PL/SQL expressions. Prior to that version developers should fetch the nextval or currval of a sequence by using a select … dual type of statements. By 11g you don’t need this anymore. You can simply refer to sequences anywhere you want within a PL/SQL expression.

In this paper you will find out basic usage example and whether this new syntax is a new PL/SQL optimization removing dual access necessity or it is only plastics.

Read the rest of paper…

About kocakahin

Just a computer engineer

Posted on September 7, 2008, in Oracle and tagged , , , . Bookmark the permalink. 2 Comments.

  1. Does this mean that you can set a columns default value in a table to be the sequence instead of doing a trigger for a PK?

  2. Sure not. This is totally obvious since it’s a compiler trick it is possible to do the change during compile time of PL/SQL code. Once you have changed and compiled it, it is done. But in a SQL DDL statement you can not do this by simply using a compiler trick, because it is a metadata definition not a living piece of code like PL/SQL. Once you try you will get the following error:

    odsbase@ODSDEV> CREATE TABLE customer
    2 (
    3 NAME VARCHAR2(50 CHAR),
    4 surname VARCHAR2(50 CHAR),
    5 ID NUMBER(6) DEFAULT customer_seq.NEXTVAL NOT NULL
    6 )
    7 /
    ID NUMBER(6) DEFAULT customer_seq.NEXTVAL NOT NULL
    *
    ERROR at line 5:
    ORA-00984:

    Regards,
    Husnu

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: