Sequence is a schema object in Oracle database that generates unique numbers. In Oracle there is not auto-generated or auto-populated number columns, so we use sequence to populate such columns. Sequences are also used in PL/SQL anywhere a fast and a unique number is required. The uniqueness is limited to the numbers that are generated by a particular sequence.
Oracle Database 10g & prior – Sequence use in PL/SQL
User of sequence in Oracle 10g and prior is a bit clumsy. You need to actually reach out to SQL to get a value from your sequence.
DECLARE
v_id NUMBER;
BEGIN
SELECT test_sequence.NEXTVAL
INTO v_id
FROM dual;
END;
/
Oracle Database 11g – Sequence use in PL/SQL
One of the language enhancements that was introduced in 11g is the use of sequence pseudocolumns, NEXTVAL and CURRVAL, in any PL/SQL context when an expression of NUMBER datatype may appear.
DECLARE
v_id NUMBER;
BEGIN
v_id := test_sequence.NEXTVAL;
END;
/
It is recommended that you avoid using the old syntax for using sequence in your code. Aside from the obvious reduced typing for developers, your code is also clearer.
Now does this also have a performance benefit? Since the sequence is a schema object, we still need to make the call to the SQL to get a value from the sequence, however this call is not done by Oracle for you.
Oracle Database 12c – Sequences
The rumour has it that a new auto-generated or auto-populated data type will make way in Oracle 12c. This will certainly reduce the use of sequences, although the sequences will still be supported by Oracle 12c. This document will be updated upon release of Oracle 12c.
Oracle 12c Update
June 25, 2013
Today Oracle released Oracle Database 12c. As per official new features documentation, we can see that there is a new column type provided for tables based on the ANSI SQL keyword IDENTITY.
This new column type automatically provides incrementing values, thereby reducing the need to sequences as well as reduced application code.
You can access Oracle Database 12c New Features document here.
We can help
eSolution provides application development services to provide you with end to end support for your development needs. We also provide code auditing and tuning services to make sure that your in-house developers meeting and exceeding industry standards and best practices.
Call us today and let us show you how we can help.
[button href=”contact” align=”center”]Contact Us Today[/button]