Sequence in Oracle 11G

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.

Read Also:-