SQL - SEQUENCES

An Oracle Sequence is a database object, just like a table or view, that represents a sequence of integers that can be used by any table or view. A Sequence’s values can be accessed using the NEXTVAL, and CURRVAL pseudo-columns. A Sequence can be ascending or descending.

Step 1 : Connect as the ADMIN user, and create a user (seq_user as example) with the CREATE USER command that we’ll use to create a Sequence

Step 2 : Connect to the user and check if the user has privilege to create sequence

Step 3 : Create a sequence with maxvalue =20, and cycle so that the values repeats after the max value

Step 4 : Create a sequence with maxvalue =20000, and nocycle

Step 5 : Create a table to generate the sequence

Step 6 : Insert values by generating sequence

Step 7 : Insert values by choosing currvalue

Repeat the step 6 until error occurs in salary column

Here the sal column has reached the maxvalue we specified in the sequence and hence error occurs.

Step 8 : Now alter the sequence to a new maxvalue

Step 9 : Insert values

Now we can see that the eno column values had reached the maxvalue and restarted the value from the beginning as we had given “cycle” option when creating the respective sequence.

Step 10 : To view the detail of the sequence created

Step 11 : Drop the sequence

« Previous Next Topic » (SQL - Indexes)