Two-stepping Sequences
Sometimes I’m amazed at things that come up. A student wondered why the sequences were incrementing by two when they’re defined to increment by one. It turns out that they were using Oracle APEX to create SQL statements to build a table, constraints, and a auto-numbering sequence trigger. Before executing the code, they’d copy it into their re-runnable script that created their schema.
Here’s an example of code that was generated by APEX for a table:
-- Create the table. CREATE TABLE onesy ( onesy_id NUMBER , onesy_text VARCHAR2(20)); -- Add the primary key constraint. ALTER TABLE onesy ADD CONSTRAINT onesy_seq PRIMARY KEY (onesy_id); -- Add a database trigger. CREATE OR REPLACE TRIGGER onesy_trg BEFORE INSERT ON onesy FOR EACH ROW BEGIN :NEW.onesy_id := onesy_s1.NEXTVAL; END; / |
This works in APEX because it doesn’t create forms that call onesy_seq.NEXTVAL
but they did create that logic in their forms. The INSERT
statement would look like:
INSERT INTO onesy VALUES (onesy_seq.NEXTVAL, 'One'); |
Therefore, the INSERT
statement incremented the trigger by one and the database trigger incremented it by one. The result is that sequences two-step, which isn’t effective or the desired behavior.
After I explained the two-step problem, they asked if they could only call the trigger when the primary key value was null. While they could do that like this:
1 2 3 4 5 6 7 8 9 | -- Add a database trigger. CREATE OR REPLACE TRIGGER onesy_trg BEFORE INSERT ON onesy FOR EACH ROW WHEN (NEW.one_id IS NULL) BEGIN :NEW.onesy_id := onesy_s1.NEXTVAL; END; / |
The problem is that this type of trigger doesn’t stop other possible problems. While it prevents two-stepping the sequence, it doesn’t prevent two other errors.
One possible error that isn’t managed in this scenario is the use of numeric literals beyond the next value of the sequence. It writes the row but eventually the sequence catches up to the higher value and a production insert would fail. It would raise the following exception.
INSERT INTO onesy (onesy_text) VALUES ('Eight') * ERROR at line 1: ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated |
Another possible error can occur when you use a bulk insert operation. Assuming you’re inserting 500 rows at a go, you query the maximum value of the onesy_id
column and then create an array of 500 numbers. Then, you perform the bulk INSERT
statement. The next call to the trigger would raise another ORA-00001
unique constraint error.
Yes, you could lock the table before you perform the bulk operation. After the bulk operation you would drop and recreate the sequence with a new value equal to the maximum value in the column, and unlock the table. This limits concurrency of operation. You could treat these bulk operations as off-line transactions (batch processing) and it would work nicely.
You could also implement a policy that no bulk operations provide generated column values that link to a sequence. Beyond it’s impracticality to manage, that type of restriction does limit the benefit of bulk operations.
The students wanted a solution. So, here’s my take on a trigger that prevents collision with values above the next sequence value. It assumes that bulk operations will be performed as batch processing where you can disable this trigger.
This trigger disallows numeric literals, logs any attempts to use them, and stops processing when an INSERT
statement tries to use anything other than the .NEXTVAL
of the sequence. It will only work in an Oracle Database 11g database because the context of using a sequence_name.CURRVAL
in a comparison isn’t supported in prior releases. The onesy
table is renamed the one
table in the example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | -- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE msg_s1; CREATE OR REPLACE TRIGGER one_t1 BEFORE INSERT ON one FOR EACH ROW DECLARE /* Define an autonomous transaction scope to the trigger. */ PRAGMA AUTONOMOUS_TRANSACTION; /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence is called before a .NEXTVAL for the same sequence in the same session. */ no_sequence_in_scope EXCEPTION; PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002); BEGIN /* Check if surrogate key is provided and the sequence not out of transaction scope. */ IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN /* Write message when sequence value is a numeric literal not a sequence generated value but a one_s1.NEXTVAL was previously called in the session. Commit after write or information is lost because it throws an user-defined exception. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].'); COMMIT; /* Stop processing by throwing exception. */ RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].'); ELSIF :NEW.one_id = one_s1.CURRVAL THEN /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */ NULL; ELSE /* Increment sequence and assign a value when one isn't provided, like a NULL value. */ :NEW.one_id := one_s1.NEXTVAL; END IF; EXCEPTION /* Handle a no sequence in scope error. */ WHEN no_sequence_in_scope THEN /* Write and commit log message for error. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].'); COMMIT; /* Stop processing by throwing an exception. */ RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].'); END; / |
Since anonymous transaction triggers are tricky, it’s important to note that the message writing requires two commits. One before raising the exception when the .CURRVAL
is in session scope and another in the exception handler before raising the error. If you forget those COMMIT
statements, this is a sample of the error stack:
INSERT INTO one VALUES (one_s1.nextval,'Six') * ERROR at line 1: ORA-06519: active autonomous TRANSACTION detected AND rolled back ORA-06512: at "STUDENT.ONE_T1", line 31 ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1' |
The trigger raises the following type of exceptions for an offending INSERT
statement. The first occurs when the sequence is valid in the session scope, like:
DECLARE * ERROR at line 1: ORA-20001: NOT a SEQUENCE generated VALUE [1]. ORA-06512: at "STUDENT.ONE_T1", line 48 ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1' ORA-06512: at line 15 |
The second occurs when the sequence isn’t valid in the session scope.
INSERT INTO one VALUES (401,'Nine') * ERROR at line 1: ORA-20002: ID provided BY calling scope IS NOT NEXT SEQUENCE VALUE [401][Nine]. ORA-06512: at "STUDENT.ONE_T1", line 24 ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1' |
A value that’s below the current high-watermark of the sequence raises a unique constraint, like this:
INSERT INTO one VALUES (1,'Eight') * ERROR at line 1: ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated |
The following is a script with all the necessary code components to test the example.
Oracle Script ↓
Unfold this if you’d like to see the complete test case.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | -- Conditionally drop tables and sequences. BEGIN FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP IF i.object_name = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / -- Create a table ONE. CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20)); -- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE one_s1; -- Create a table MSG. CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80)); -- Create a sequence for table ONE that starts with 1 and increments by 1. CREATE SEQUENCE msg_s1; -- Create or replace trigger. CREATE OR REPLACE TRIGGER one_t1 BEFORE INSERT ON one FOR EACH ROW DECLARE /* Define an autonomous transaction scope to the trigger. */ PRAGMA AUTONOMOUS_TRANSACTION; /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence is called before a .NEXTVAL for the same sequence in the same session. */ no_sequence_in_scope EXCEPTION; PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002); BEGIN /* Check if surrogate key is provided and the sequence not out of transaction scope. */ IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN /* Write message when sequence value is a numeric literal not a sequence generated value but a one_s1.NEXTVAL was previously called in the session. Commit after write or information is lost because it throws an user-defined exception. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].'); COMMIT; /* Stop processing by throwing exception. */ RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].'); ELSIF :NEW.one_id = one_s1.CURRVAL THEN /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */ NULL; ELSE /* Increment sequence and assign a value when one isn't provided, like a NULL value. */ :NEW.one_id := one_s1.NEXTVAL; END IF; EXCEPTION /* Handle a no sequence in scope error. */ WHEN no_sequence_in_scope THEN /* Write and commit log message for error. */ INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].'); COMMIT; /* Stop processing by throwing an exception. */ RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].'); END; / -- Define a schema collection of strings. CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20); / -- Define a schema collection of strings. CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20); / -- Anonymous block bulk testing program. DECLARE -- Declare two scalar collection variables. lv_index ONE_INDEX := one_index(); lv_list ONE_LIST := one_list('One','Two','Three','Four','Five'); BEGIN -- Initialize the collection of the ONE_INDEX collection. lv_index.EXTEND(lv_list.COUNT); FOR i IN lv_list.FIRST..lv_list.LAST LOOP dbms_output.put_line('['||i||']['||lv_list(i)||']'); lv_index(i) := i; END LOOP; -- Perform a trivial example of a bulk insert. FORALL i IN lv_list.FIRST..lv_list.LAST INSERT INTO one VALUES (lv_index(i),lv_list(i)); END; / -- Testing INSERT statements until sequence value increments beyond bulk insert values. INSERT INTO one VALUES (6,'Six'); INSERT INTO one VALUES (one_s1.nextval,'Seven'); INSERT INTO one VALUES (1,'Eight'); INSERT INTO one VALUES (401,'Nine'); INSERT INTO one VALUES (NULL,'Ten'); COLUMN msg_id FORMAT 999 HEADING "Msg|ID" COLUMN msg_text FORMAT A72 HEADING "Msg Text" -- SELECT * FROM one; SELECT * FROM msg; -- Anonymous block bulk testing program. DECLARE -- Declare two scalar collection variables. lv_list ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five'); BEGIN -- Perform a trivial example of a bulk insert. FORALL i IN lv_list.FIRST..lv_list.LAST INSERT INTO one VALUES (NULL,lv_list(i)); END; / -- Query tables. SELECT * FROM one; SELECT * FROM msg; |
If I’ve fat fingered any typing or made logical errors, please let me know.