About
This page is about the management of a relational sequence in Oracle.
A highly scalable sequence because it's:
- sorted.
- non-blocking ID generator.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
CREATE SEQUENCE MY_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;
Articles Related
Primary Key Trigger
create or replace TRIGGER "TRIGGER_NAME" BEFORE INSERT ON "TABLE_NAME" FOR EACH ROW
BEGIN
IF :new.PK_ID IS NULL THEN
select SEQUENCE_NAME.nextval into :new.PK_ID from dual;
END;
END;
Bad Practice
With a max
INSERT INTO gfx_suggestion (suggestion, suggestion_id, timestamp,
suggestion_type_fl,
name, email, business_unit_key) select 'test suggestion. RJ 04/19/01',
max(suggestion_id)+1, sysdate, 'T', 'Ron Jennings' , '[email protected]', '5' from
gf_suggestion
When two people insert at about the same time with a max function, they both get the SAME suggestion_id.