Sequences in Oracle

Certain columns can be configured to draw their values from a sequence, for example:

CREATE SEQUENCE MY_USER.MY_SEQUENCE START WITH 11 INCREMENT BY 1;

CREATE TABLE MY_USER.MY_TABLE (
    ID NUMBER DEFAULT MY_USER.MY_SEQUENCE.NEXTVAL PRIMARY KEY,
    NAME VARCHAR2(100)
);

For certain transformation configurations, these values may be inserted directly into the output table, thus bypassing the sequence. As the result, the current sequence value in the same output is not incremented. In such cases, when attempting to insert a new record with a default value into the table MY_USER.MY_TABLE:

INSERT INTO MY_USER.MY_TABLE (NAME) VALUES ('NAME');

an ORA-00001: unique constraint violated error may occur.

If a sequence needs to be restarted at a different number, you can either drop and recreate it or use the ALTER SEQUENCE statement with RESTART and START WITH parameters (for Oracle 18 and later).

Dropping and recreating the sequence

Drop and recreate a sequence with the previous parameters and the START WITH value that is the next value after maximum of ID column value from the table MY_USER.MY_TABLE. Example of the script:

DECLARE
    MAX_ID NUMBER;
BEGIN
    SELECT COALESCE(MAX(ID), 0) + 1 INTO MAX_ID FROM MY_USER.MY_TABLE;
    EXECUTE IMMEDIATE 'DROP SEQUENCE MY_USER.MY_SEQUENCE';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_USER.MY_SEQUENCE START WITH ' || MAX_ID;
END;

Pay attention that when you drop and recreate a sequence in Oracle, all previously granted privileges on that sequence are lost, so you should re-grant them after recreating the sequence. More information about the DROP SEQUENCE statement can be found in the documentation.

Using ALTER SEQUENCE statement

This script updates MY_USER.MY_SEQUENCE sequence value in place without dropping it:

DECLARE
    MAX_ID NUMBER;
BEGIN
    SELECT COALESCE(MAX(ID), 0) + 1 INTO MAX_ID FROM MY_USER.MY_TABLE;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_USER.MY_SEQUENCE RESTART START WITH ' || MAX_ID;
END;

This approach works only in Oracle 18c and later, where ALTER SEQUENCE … RESTART is supported. More information about the ALTER SEQUENCE statement can be found in the documentation.

Post execution script

To automate sequence updates during the transformation process, it can be added as a post execution script in the .yaml configuration file.

Example of .yaml configuration file:

default_config:
  mode: KEEP
table_truncation_mode: TRUNCATE
schema_creation_mode: CREATE_IF_NOT_EXISTS
safety_mode: RELAXED
scripts:
  post:
    source: INLINE
    script: |
        DECLARE
            MAX_ID NUMBER;
        BEGIN
            SELECT COALESCE(MAX(ID), 0) + 1 INTO MAX_ID FROM MY_USER.MY_TABLE;
            EXECUTE IMMEDIATE 'ALTER SEQUENCE MY_USER.MY_SEQUENCE RESTART START WITH ' || MAX_ID;
        END;