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.
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?
Comment by Tom — September 7, 2008 @ 5:45 pm |
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
Comment by kocakahin — September 7, 2008 @ 6:19 pm |