In Oracle, the sequences object provides a sequential series of numbers to applications without the overhead of disk I/O or transaction locking. These values are often used for primary and unique keys.
In the previous release of Oracle Database, You can refer the sequence values in SQL statements with following pseudocolumns:
CURRVAL
returns the current value of a sequence.
NEXTVAL
increments the sequence and returns the next value.
Example:
SELECT employeeid.NEXTVAL
INTO l_employeeid
FROM DUAL;
But setback was that these pseudocolumns cannot be used directly in PL/SQL.
Some time this can be quite irritating from programmer’s point of view.
In Oracle Database 11g, it is now possible to simply use the pseudocolumns CURRVAL and NEXTVAL in a PL/SQL expression.
Example:
l_employeeid := employeeid.NEXTVAL;
4 responses so far ↓
1 Asif Momen // Sep 25, 2007 at 4:21 am
Hi,
Oracle internally translates the assignment into equivalent SQL statement. Read this for a test case.
Momen
2 Ray DeBruyn // Dec 7, 2007 at 1:45 am
Personally, I wouldn’t bother using this feature. Oracle is doing a select to get the sequence value under the covers. Code written in 11g wouldn’t be backward compatible. For something that gives no performance benefit, I can’t see using it for now.
3 Rajender Singh // Dec 13, 2007 at 11:29 pm
Hi Asif,
That was really great finding by you!
Regards,
Raj
4 Rajender Singh // Dec 13, 2007 at 11:31 pm
Hi Ray,
You are right!
From performance point of view there is no benefit but when writting the code I do see some benefit as we don’t have to write whole code!
Regards.
Raj
Leave a Comment