Oracle Sequence | John & Kathy Purcell

Sequence

List

DESCRIBE user_sequences
select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,
INCREMENT_BY, LAST_NUMBER from user_sequences;

Create

create sequence ORDER_HEADER_seq
start with 1 increment by 1
minvalue 1 maxvalue 9999999999999
cycle nocache noorder;

Use

Next Value
select PRODUCT_SEQ.NEXTVAL from dual;

Current Value
select PRODUCT_SEQ.CURRVAL from dual;

Delete

drop sequence ORDER_HEADER_seq;

Hold onto a Sequence Value

Basic Insert
INSERT INTO testtable
(field1, field2, field3, field4, field5, field7)
VALUES
('junk', testtable_SEQ.NEXTVAL, 'A', 1, 'more junk', sysdate);

Get Current Sequence Value
COLUMN p OLD_VALUE testtable_SEQ
SELECT testtable_SEQ.CURRVAL p FROM dual;
PROMPT testtable_SEQ.CURRVAL IS &&testtable_SEQ

Use Held Sequence Value
INSERT INTO testtable2 (field1, field2)
VALUES('junk', &&testtable_SEQ);

commit;
exit;