how to create sequence

How to create 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 generate numeric values and it can be unique. you can use this value into primary key and unique values. Let see how to create SEQUENCE and how to use sequence step by step.

Click here to read about Single Row Functions

How to create SEQUENCE?

To create sequence use below query.

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

Sequence created.

After creating sequence now i’m going to create a table and insert some records into this table using 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 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 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 inserting 11th row it’ll be showing an error look 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 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 below query.

SQL> ALTER SEQUENCE OCP
          MAXVALUE 200;

Sequence altered.

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

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

SQL> SELECT OCP.CURRVAL FROM DUAL;

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

NEXTVAL is help us to find NEXT future VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

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

For more detail please scroll and watch YouTube video.

 

Share this
Share

2 thoughts on “How to create SEQUENCE step by step”

Leave a Comment

Share