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

☛ Join to Learn from Experts: Oracle SQL Training in Chennai by TesDBAcademy
(SQL - Indexes)