Use of Oracle Sequences in PL/SQL Expressions

In this post, I will be discussing yet another new feature of Oracle database 11g. Well, this new feature is related to Oracle Sequences. With Oracle Database 11g, we can use SEQUENCE.NEXTVAL in PL/SQL expression instead of writing a SELECT statement. Here is an example:




Using SEQUENCE.NEXTVAL in PL/SQL assignment simply enhances readability, but, under the covers, Oracle rewrites the assignment into a SELECT statement. To verify the same, I traced a small PL/SQL block using 10046 trace as shown below:

Following is an extract from the trace file:

As you may see, Oracle has re-written the expression into equivalent SQL statement. Performance wise, this new feature does not give any benefit but the advantage we get is readability.

Thanks for reading :)