A CYCLE sequence

 A CYCLE sequence

SQL> -- A CYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2 INCREMENT BY 50000
  3 START WITH 50000
  4 MAXVALUE 99999
  5 NOCACHE
  6 CYCLE;

Sequence created.

SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50000

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  1

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50001

SQL>
SQL>
SQL> drop sequence studentNumSeq;



Note:-Please comment and reply me.

Read More

Modifying a Sequence

Modifying a Sequence

• You must be the owner or have the ALTER privilege for the sequence.

• Only future sequence numbers are affected.

• The sequence must be dropped and re-created to restart the sequence at a different number.

• Some validation is performed.
 

 
Note:-Please comment and reply me.

Read More

NOCYCLE sequence

SQL> -- A NOCYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2 INCREMENT BY 50000
  3 START WITH 50000
  4 MAXVALUE 99999
  5 NOCACHE
  6 NOCYCLE;

Sequence created.

SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50000

SQL> select studentNumSeq.nextVal from dual;
select studentNumSeq.nextVal from dual
  *
ERROR at line 1:
ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL> select studentNumSeq.nextVal from dual;
select studentNumSeq.nextVal from dual
*
ERROR at line 1:
ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL>
SQL>
SQL> drop sequence studentNumSeq;

Sequence dropped.


Note:-Please comment and reply me.

Read More

Alter sequence increment

 Alter sequence increment

SQL> CREATE SEQUENCE mySequence
  2 MINVALUE 1
  3 MAXVALUE 999999999999999999999999999
  4 START WITH 1
  5 INCREMENT BY 1
  6 CACHE 20;

Sequence created.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  1

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  2

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  3

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  4

SQL>
SQL> alter sequence mySequence
  2 increment by 124;

Sequence altered.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  128

SQL>
SQL> alter sequence mySequence
  2 increment by 1;

Sequence altered.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  129

SQL>
SQL>
SQL> drop sequence mySequence;

Sequence dropped.

SQL>
SQL>


Note:-Please comment and reply me.

Read More

Sequence Example 3

 Create sequence emps_seq start with 501 increment by 10

SQL>
SQL> create sequence emps_seq
  2 start with 501
  3 increment by 10;

Sequence created.

SQL>
SQL> drop sequence emps_seq;

Sequence dropped.

SQL>
SQL>


Note:-Please comment and reply me.

Read More

Sequence Example 2

SQL> create sequence deptno_seq
  2 start with 50 increment by 10;

Sequence created.

SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval
  2 from dual;

  NEXTVAL CURRVAL
---------- ----------
  50 50

SQL>
SQL> select deptno_seq.currval
  2 from dual;

  CURRVAL
----------
  50

SQL>
SQL> select deptno_seq.currval, deptno_seq.nextval
  2 from dual;

  CURRVAL NEXTVAL
---------- ----------
  60 60

SQL>
SQL>
SQL> drop sequence deptno_seq;

Sequence dropped.


Note:-Please comment and reply me.

Read More

If id is null, use the value from sequence

If id is null, use the value from sequence
 
SQL> create table myTable(
  2 id number primary key,
  3 blob_content blob
  4 )
  5 /

Table created.

SQL>
SQL> create sequence myTable_seq
  2 /

Sequence created.

SQL>
SQL> create or replace trigger biu_myTable
  2 before insert or update on myTable
  3 for each row
  4 begin
  5 if :new.id is null then
  6 select myTable_seq.nextval into :new.id from dual;
  7 end if;
  8 end;
  9 /

Trigger created.

SQL> drop table myTable;

Table dropped.

SQL> drop sequence myTable_seq;

Sequence dropped.




Note:-Please comment and reply me.

Read More