Sequence in Oracle 12C

Use of Sequence in oracle 12c:-

Same like IDENTITY clause, oracle has come up with same functionality with DEFAULT clause. We can specify default numeric value for the numeric data type column.

By using sequence, we can specify NEXTVAL and CURVAL (pseudo columns) as a default value for a column.

Few things to remember about using sequence pseudo columns as defaults include:

• During table creation, the sequence must exist and you must have select privilege on it for it to be used as a default column.
• The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table.
• If the sequence is dropped after table creation, subsequent inserts will occur.
• Sequences used as default values are always stored in the data dictionary with fully qualified names. Normal name resolution rules are used to determine the sequence owner, including expansion of private and public synonyms.

As with use of any sequence, gaps in the sequence of numbers can occur for a number of reasons. For example, if a sequence number is requested and not used, a statement including a sequence is rolled back, or the databases are turned off and cached sequence values are lost. 

Below is an example of DEFAULT clause:-

To perform this activity, user must have CREATE SEQUENCE and CREATE TABLE privileges.

Read Also:-