CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Sequences
Version 11.1
General
Dependent Objects
seq$    
dba_sequences all_sequences user_sequences
Related System Privileges create sequence
create any sequence
alter any sequence
drop any sequence
select any sequence
NOTE: The alternative to sequences used in other RDBMS products is autonumbering and keeping the current number in a table. Both of these other methods demand serialization as they can only dispense one number at a time. Table example:

CREATE TABLE seqnum (
next_number  NUMBER(1);

1. Lock the seqnum table for your transaction
2. Select next_number from seqnum;
3. Update seqnum SET next_number=next_number+1;
4. Unlock the seqnum table for the next transation
 

Tables For Sequence Demos
CREATE TABLE campus_site (
site_id           NUMBER(4),
organization_name VARCHAR2(40),
campus_name       VARCHAR2(30),
address_id        NUMBER(10));

CREATE TABLE division (
division_id   NUMBER(5),
site_id       NUMBER(4),
division_name VARCHAR2(40),
address_id    NUMBER(10));

CREATE TABLE department (
department_id   NUMBER(5),
division_id     NUMBER(5),
department_name VARCHAR2(40),
address_id      NUMBER(10));

CREATE TABLE seq_test (
test NUMBER(10));
 
Create Sequence

Full Create Sequence Syntax
CREATE SEQUENCE <sequence_name>
INCREMENT BY <integer>
START WITH <integer>
MAXVALUE <integer> / NOMAXVALUE
MINVALUE <integer> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;

Create Sequence Simplest Form
CREATE SEQUENCE <sequence_name>;
CREATE SEQUENCE seq_campus_site_id;

SELECT seq_campus_site_id.NEXTVAL FROM DUAL;
/
/

Simple Autonumber With Sequence
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.NEXTVAL);
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Main Seattle');

SELECT *
FROM campus_site;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Bothell');

SELECT *
FROM campus_site;

Simple Autonumber With Sequence Into Two Tables

Thanks Milo van der Leij for the correction

INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.CURRVAL);
CREATE SEQUENCE seq_division_id;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Tacoma');

INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_division_id.NEXTVAL, seq_campus_site_id.CURRVAL, 'Engineering');

SELECT *
FROM campus_site;

SELECT *
FROM division;

Simple Transaction Number For Audit Demoing START WITH and a caution with CURRVAL
CREATE SEQUENCE <sequence_name> START WITH <integer>;
CREATE SEQUENCE seq_audit_tx START WITH 297;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett');

INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Science');

INSERT INTO department
(department_id, division_id, department_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Astronomy');

SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department;

ROLLBACK;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett');

INSERT INTO division
(site_id, division_id, division_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Science');

INSERT INTO department
(division_id, department_id, department_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Astronomy');

SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department;

INCREMENT BY
CREATE SEQUENCE <sequence_name> INCREMENT BY <positive integer>;
CREATE SEQUENCE seq_inc_by_two INCREMENT BY 2;

INSERT INTO seq_test VALUES (seq_inc_by_two.NEXTVAL);
/
/

SELECT * FROM seq_test;

CREATE SEQUENCE seq_inc_by_ten INCREMENT BY 10;

INSERT INTO seq_test VALUES (seq_inc_by_ten.NEXTVAL);
/
/

SELECT * FROM seq_test;

ALTER TABLE seq_test ADD test2 NUMBER(10);

desc seq_test

INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.NEXTVAL);

SELECT * FROM seq_test;

INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.CURRVAL);

SELECT * FROM seq_test;

Reverse DECREMENT BY
CREATE SEQUENCE <sequence_name>
MAX VALUE <integer value>
INCREMENT BY <negative integer>;
CREATE SEQUENCE seq_reverse INCREMENT BY -5;

ALTER TABLE seq_test DROP COLUMN test2;

INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/

SELECT * FROM seq_test;

DROP SEQUENCE seq_reverse;

CREATE SEQUENCE seq_reverse MAXVALUE 150
START WITH 150 INCREMENT BY -5;

INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/

SELECT * FROM seq_test;

MAXVALUE Demo
CREATE SEQUENCE <sequence_name> START WITH <integer> 
MAXVALUE <integer>;
CREATE SEQUENCE seq_maxval START WITH 1 MAXVALUE 5;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
/
/
/

SELECT * FROM seq_test;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);

SELECT * FROM seq_test;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);

CACHE Demo
CREATE SEQUENCE <SEQUENCE_name> CACHE <integer>;
CREATE SEQUENCE seq_cache CACHE 100;

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM DUAL;

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM DUAL;
/

SELECT sequence_name, last_number
FROM user_sequences;

conn / as sysdba

shutdown abort;

startup

conn uwclass/uwclass

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM DUAL;

CYCLE Demo
CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer> CYCLE;
CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE;
-- default cache is 20

CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE CACHE 4;

TRUNCATE TABLE seq_test;

INSERT INTO seq_test VALUES (seq_cycle.NEXTVAL);
/
/
/
/
/
/

SELECT * FROM seq_test;

ORDER Demo
CREATE SEQUENCE <sequence_name> START WITH 1 ORDER;
CREATE SEQUENCE seq_order START WITH 1 ORDER;
 
Alter Sequence
Change Increment ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;
Change Max Value ALTER SEQUENCE <sequence_name> MAX VALUE <integer>
ALTER SEQUENCE seq_maxval MAXVALUE 10;
Change Cycle ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>
ALTER SEQUENCE seq_cycle NOCYCLE;
Change Cache ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE
ALTER SEQUENCE seq_cache NOCACHE;
Change Order ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>
ALTER SEQUENCE seq_order NOORDER;
 
Drop Sequence
Drop Sequence DROP SEQUENCE <sequence_name>;
DROP SEQUENCE seq_cache;
 
Using A Sequence

In an Insert
CREATE TABLE t (
col1 NUMBER(5),
col2 NUMBER(5));

CREATE SEQUENCE seq;

INSERT INTO t
(col1, col2)
VALUES
(seq.NEXTVAL, seq.CURRVAL);

/

/

SELECT * FROM t;

Traditional PL/SQL
set serveroutput on

DECLARE
 i NATURAL;
 j NATURAL;
BEGIN
  SELECT seq.NEXTVAL, seq.CURRVAL
  INTO i, j
  FROM DUAL;

  dbms_output.put_line(i);
  dbms_output.put_line(j);
END;
/

/

PL/SQL in 11g
set serveroutput on

BEGIN
  dbms_output.put_line(seq.NEXTVAL);
  dbms_output.put_line(seq.CURRVAL);
END;
/

/
 
Sequence Resets
By finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- the sequence can be reset to 0.

If any session attempts to use the sequence while this is happening an ORA-08004 error will be generated.
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM DUAL;
SELECT seq.NEXTVAL FROM DUAL;
SELECT seq.NEXTVAL FROM DUAL;

COLUMN S new_val inc;
SELECT seq.NEXTVAL S FROM DUAL;

ALTER SEQUENCE seq INCREMENT BY -&inc MINVALUE 0;
SELECT seq.NEXTVAL S FROM DUAL;

ALTER SEQUENCE seq increment by 1;
SELECT seq.NEXTVAL FROM DUAL;
/
/

Stored Procedure Method
CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS

cval   INTEGER;
inc_by VARCHAR2(25);

BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';

  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'
  INTO cval;

  cval := cval - startvalue + 1;
  IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF;
   
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||
  cval;

  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM DUAL'
  INTO cval;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||
  ' INCREMENT BY 1';

END reset_sequence;
/
 
Sequence Related Queries
Last Number Selected From Sequence SELECT sequence_name, last_number
FROM user_sequences;
Next Number From Sequence SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [62 users online]    © 2010 psoug.org