Sequence in Oracle

Sequence:-

Scenario:-

Suppose, you are required to generate unique number, which you need to use as a primary key or generate sequential number for your report. In such a case you can use sequence which generates unique number.

What is sequence?

Sequence is an object in oracle which generates unique auto generated number.

Syntax of the sequence:-

CREATE SEQUENCE SEQUENCE_NAME

MINVALUE VALUE

START WITH VALUE

 [MAXVALUE VALUE]

 INCREMENT BY VALUE

 CACHE VALUE / NO CACHE

CYCLE / NOCYCLE;

 Here:-

MINVALUE: - Is the minimum value of the sequence. This values must be less than or equal to start with value.

START WITH VALUE: - Is the value from which sequence will start generating values.

MAXVALUE: - Is the value after which you don’t require to generate value.

INCREMENT BY VALUE: - Is a value by which you need to increment your number.

CACHE VALUE: - Specify how many values of the sequence the database reallocates and keeps in memory for faster access.

CYCLE VALUE: - Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value.

Let’s try to create a sequence:-

CREATE SEQUENCE TEST_SEQ

MINVALUE 1

START WITH 1

INCREMENT BY 1

NOCACHE;

You have created sequence now you need to retrieve its next value. To achieve this, there are pseudo columns in oracle by which you can access sequence.

1)    Nextval

2)    Currval

·         NEXTVAL:-

Next value (nextval) is the pseudo column which will help you to retrieve sequence next value. When you want to use the sequence with its next value you can use nextval pseudo column. Like shown below:-

Syntax to retrieve next value is:-

SEQUENCE_NAME.NEXTVAL;

In our case we have created TEST_SEQ, so we can retrieve its next value like:-

SELECT TEST_SEQ.NEXTVAL FROM DUAL;

Whenever you will fire this query you will get new number or say unique number and hence this can be used as primary key since it generates 100 percent unique number.

·         Currval

Current value (currval) is the pseudo column which will help you to retrieve sequence current value. When you want to use the sequence and you don’t know the current value of the sequence then you can query that sequence with curval pseudo column to find out what it the current number that sequence it holding. You can do this like below:-

Syntax is:-

SEQUENCE_NAME.CURVAL;

In our case we have created TEST_SEQ, let’s see what current number this sequence is holding is:-

SELECT TEST_SEQ.CURRVAL FROM DUAL;

Above its showing 3 since after 3 we have not retrieved any value.

Using sequence in oracle totally depends on the version in which you are using sequence. In 10G, there are less options, in 11G oracle have come up with enhancement and in 12G it’s very easy to work with sequence. Below we have example with each version.

Use of Sequence in oracle 11G:-

You can create an auto number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Starting with Oracle 11g, we can use sequences with straight variable assignment. Before 11g, we always have to use SELECT INTO clause to get sequence value in the variable. It is not a major change but just makes it easier to use. Let us see it with an example. We will first create a sequence.

Here:-

sequence_name:- The name you want the sequence to have. This may include the user name if created from an account with DBA privilege. An integer, positive or negative.

INCREMENT BY: - Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending.

START WITH: - Tells the system which integer to start with.

MINVALUE: - Tells the system how low the sequence can go. For ascending sequences, it defaults to 1; for descending sequences, the default value is 10e27-1.

MAXVALUE:-Tells the system the highest value that will be allowed. For descending sequences, the default is 1; for ascending sequences, the default is 10e27-1.

CYCLE:- Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.

CACHE: - Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.

 We have created a sequence successfully.

Now we will try to use this sequence in Oracle 10 G.

For assigning the value to declared variable we use SELECT INTO clause.

Let’s try to assign a value to variable without using SELECT INTO clause:-

It’s throwing Error. That means it’s not supported In Oracle 10 G.

Let’s try it in Oracle 11 G:-

Yes, In Oracle 11 G it’s working fine.

Use of Sequence in oracle 12G:-

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.

Difference between Rank, Dense_Rank and Row_Number