Tutorial 4: Sequences

So far, we’ve just looked at tables, constraints, indexes, and data. But what about other objects. Sequences are often an essential requirement for populating primary keys. Common approaches to populating primary keys are via triggers or, for post Oracle 12c applications, identity columns.

1. Create a new table for scott, with a trigger.

   Looking at a table with primary key populated with a sequence via a trigger first. Create scott a new table, salgrade1.

   Create the following table and sequence in the Oracle scott schema:

   CREATE TABLE salgrade1
   ( salgrade1_id  NUMBER(10,0) CONSTRAINT pk_salgrade1 PRIMARY KEY,
     grade         NUMBER(2,0),
     losal         FLOAT,
     hisal         NUMBER
   );
   
   CREATE SEQUENCE salgrade1_seq START WITH 1000 INCREMENT BY 1;
   

   Now create the following trigger, again in the Oracle scott schema:

   CREATE OR REPLACE TRIGGER salgrade1_seqtrg
   BEFORE INSERT ON salgrade1
   FOR EACH ROW
   BEGIN
     :NEW.salgrade1_id := salgrade1_seq.NEXTVAL;
   END;
   /
   

   Finally insert some data:

   INSERT INTO salgrade1 (grade,losal,hisal) VALUES (1,700,1200);
   INSERT INTO salgrade1 (grade,losal,hisal) VALUES (2,1201,1400);
   INSERT INTO salgrade1 (grade,losal,hisal) VALUES (3,1401,2000);
   INSERT INTO salgrade1 (grade,losal,hisal) VALUES (4,2001,3000);
   INSERT INTO salgrade1 (grade,losal,hisal) VALUES (5,3001,9999);
   COMMIT;
   

2. Link the table and sequence.

   In PostgreSQL the trigger to populate the column with the sequence is not required. A sequence can be referenced directly by a column, similar to Oracle’s identity column. For example:

   SALGRADE1_ID INTEGER NOT NULL DEFAULT NEXTVAL('SALGRADE1_SEQ')
   

   To get easyo2p to do this for us, we need to provide a sequence-column map. We do this using a parameter. Add the SEQUENCES element to the parameters declaration in the go function of the modified base.py script.

   ...
   parameters = {
       easyo2p.SEQUENCES: ['SALGRADE1_SEQ SALGRADE1.SALGRADE1_ID'],
       ...
   

   The SEQUENCES parameter is a list, with each element being a sequence to include in the migration. To link a column to the sequence, add a space, then the table and column name.

   Note

   When the SEQUENCES parameter is not supplied, all sequences will be included by default. But when present only those sequences in the list or linked to an identity column will be included.

3. Run the script.

   Run the base.py script again. Once complete the migration should now include the salgrade1 table and sequence.

4. Create a new table for scott, with an identity column.

   Note

   Identity columns were introduced in Oracle version 12, so the following steps will throw errors on Oracle 11 and prior. Skip this step if it’s not applicable.

   Identity columns are a little simpler, as the column and sequence are already linked.

   Create the following table in the Oracle scott schema:

   CREATE TABLE salgrade2
   ( salgrade2_id  INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
     grade         NUMBER(2,0),
     losal         FLOAT,
     hisal         NUMBER
   );
   

   And insert some data:

   INSERT INTO salgrade2 (grade,losal,hisal) VALUES (1,700,1200);
   INSERT INTO salgrade2 (grade,losal,hisal) VALUES (2,1201,1400);
   INSERT INTO salgrade2 (grade,losal,hisal) VALUES (3,1401,2000);
   INSERT INTO salgrade2 (grade,losal,hisal) VALUES (4,2001,3000);
   INSERT INTO salgrade2 (grade,losal,hisal) VALUES (5,3001,9999);
   COMMIT;
   

5. Run the base.py script

   Sequences linked to columns are automatically created by easyo2p, so there’s no need to modify the parameters, so just run base.py again.

   Once complete the migration should now include the salgrade2 table.