How to create sequence step by step

Hello Friends in this tutorial i'm going to demonstrate you how to create sequence and how to use ORACLE - SQL sequence step by step.

ORACLE – SQL TUTORIAL STEP BY STEP.

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 user sequence step by step, following below steps.

STEP 1. To create sequence step by step.

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

Sequence created.

After creating sequence i’m going to create a new table and insert some records into table using this sequence step by step following command.

STEP 2. create a table using following command step by step.

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

Table created.

Now you done table creation and sequence creation after done that now i’m going to show you how to insert records in that table using sequence, for more clarification you can view my video on my YouTube channel and if get any knowledge from my video so subscribe my channel and like my video and if you want to give any suggestion regarding my contents and my videos so please write in comment box and you can also send my E-mails on my E-mail id ( ocptechno@gmail.com ).

STEP 3.

Let see how to insert records into table using sequence following commands.

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 when we are 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 see records from your table so it’s showing like this, using below command step by step.

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.

STEP 4.

so if you want to insert another some records into this table, then you have to increase maxvalue, using below command step by step.

SQL> ALTER SEQUENCE OCP
     MAXVALUE 200;

Sequence altered.

After setting max-value of sequence, you can insert more rows into your table.

And if you want to check your sequence current value and coming feature value then you can use below command step by step.

CURRVAL is use for the knowing current value of the sequence.

SQL> SELECT OCP.CURRVAL FROM DUAL;

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

NEXTVAL is use for the knowing NEXT feature VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

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

For more detail please scroll and see YouTube video.

www.ocptechnology.com



www.youtube.com/user/ocptechno