How to create a SEQUENCE step by step

In this article, we are going to learn how to create and use SEQUENCE.

What is SEQUENCE?

SEQUENCE is an oracle shareable object which is generated numeric values and it can be unique. you can use this value as a primary key and unique value. Let see how to create SEQUENCE and how to use sequence step by step.

How to create SEQUENCE?

To create a sequence use the below query.

SQL> create sequence ocp
     minvalue 10
     start with 10
     increment by 10
     maxvalue 100
     nocycle
     nocache;

Sequence created.

After creating the sequence now I’m going to create a table and insert some records into this table using the above-created sequence step by step.

Use below query to create a table.

SQL> create table acer(roll_num number,stu_name varchar2(11));

Table created.

Now we have done table creation and sequence creation after doing this, Now I’m going to show you how to insert records in the above table using sequence, for more clarification you can watch a video on my YouTube channel.

How to insert records in table using sequence?

Using the following query we can insert records automatically in a particular column using sequence.

SQL> insert into acer values(ocp.nextval,'&stu_name');
Enter value for stu_name: AMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AMIT'1 row created.SQL> /
Enter value for stu_name: SUMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SUMIT')

1 row created.

SQL> /
Enter value for stu_name: ARSH
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ARSH')

1 row created.

SQL> /
Enter value for stu_name: DEEP
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'DEEP')

1 row created.

SQL> /
Enter value for stu_name: SACHIN 
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SACHIN')

1 row created.

SQL> /
Enter value for stu_name: VIKRAM
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'VIKRAM')

1 row created.

SQL> /
Enter value for stu_name: RAHUL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'RAHUL')

1 row created.

SQL> /
Enter value for stu_name: AJAY
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AJAY')

1 row created.

SQL> /
Enter value for stu_name: TANIYA
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'TANIYA')

1 row created.

SQL> /
Enter value for stu_name: SHRIPAL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SHRIPAL')

1 row created.

After inserting 10 rows in this table, if we are trying to insert the 11th row it’ll be showing an error that looks like this. because of that maxvalue exceeds.

SQL> /
Enter value for stu_name: ANKUR
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ANKUR')
insert into acer values(ocp.nextval,'ANKUR')
 *ERROR at line 1:
ORA-08004: sequence OCP.NEXTVAL exceeds MAXVALUE and cannot be instantiated

If you select records from your table, it will be showing like this, using the below query.

SQL> SELECT * FROM ACER;

ROLL_NUM STU_NAME
------   --------
 10      AMIT
 20      SUMIT
 30      ARSH
 40      DEEP
 50      SACHIN
 60      VIKRAM
 70      RAHUL
 80      AJAY
 90      TANIYA
 100     SHRIPAL

10 rows selected.

So, if you want to insert more records into this table, then you need to increase maxvalue parameters, using the below query.

SQL> ALTER SEQUENCE OCP
          MAXVALUE 200;

Sequence altered.

After updating the maxvalue parameter, you can insert more rows into your table and if you want to check your sequence's current value and coming future value then you can use the below query.

CURRVAL is help us to find the current value of the sequence.

SQL> SELECT OCP.CURRVAL FROM DUAL;

CURRVAL
----------
 100

NEXTVAL is helping us to find NEXT future VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

NEXTVAL
----------
 110

Read - ORACLE ALTER TABLE MODIFY