Automatically generate sequences and triggers for all tables in Oracle

Question

In my schema, I've migrated about 250 tables from SQL Server to Oracle. The thing is, no sequences or triggers have been created for any of these tables.

Is there an easy way to generate all the table sequences and triggers rather than manually doing this for every table?

An example of a sequence I need would be:

CREATE SEQUENCE "SYSTEM"."SEC_USERS_ID_SEQ"  
    MINVALUE 0 MAXVALUE 999999999999999999999999 
    INCREMENT BY 1 
    START WITH 23 
    CACHE 20 
    NOORDER NOCYCLE NOPARTITION;

And the trigger:

create or replace TRIGGER SEC_USERS_TRIG 
before INSERT 
ON "SYSTEM"."SEC_USERS" 
FOR EACH row 
BEGIN 
    IF inserting THEN 
       IF :NEW."ID" IS NULL THEN
          SELECT SEC_USERS_ID_SEQ.nextval INTO :NEW."ID" FROM dual;
       END IF;
    END IF;
END;

Answer 1

We can generate scripts using the Oracle data dictionary views (the equivalent of MSSQL INFORMATION_SCHEMA). Find out more.

This example generates CREATE SEQUENCE statements. I have followed your example and accepted the default values, which don't need to be coded. The sequence name is derived from table name concatenated with column name and suffixed with "_SEQ". Watch out for Oracle's thirty character limit on object names!

This loop dynamically queries the table to get the current maximum value of the Primary Key column, which is used to derive the STARTS WITH clause.

declare
    curr_mx number;
begin
    for lrec in ( select ucc.table_name
                         , ucc.column_name
                  from user_constraints uc
                       join user_cons_columns ucc
                           on ucc.table_name = uc.table_name
                          and ucc.constraint_name = uc.constraint_name
                       join user_tab_columns utc
                          on utc.table_name = ucc.table_name
                          and utc.column_name = ucc.column_name
                  where uc.constraint_type = 'P' -- primary key
                  and   utc.data_type = 'NUMBER' -- only numeric columns
                  )
    loop
        execute immediate 'select max ('|| lrec.column_name ||') from ' ||lrec.table_name 
            into curr_mx;
        if curr_mx is null then
            curr_mx := 0;
        end if;
        dbms_output.put_line('CREATE SEQUENCE "'|| user || '"."'
            || lrec.table_name ||'_'|| lrec.column_name || '_SEQ" '
            ||' START WITH ' || to_char( curr_mx + 1 )  ||';'
        );
    end loop;
end;
/

This code uses DBMS_OUTPUT, so you can spool it to a file for later use. If you're using an IDE like SQL Developer you may need to enable DBMS_OUTPUT. Follow the guidance in this StackOverflow answer.

If you can guarantee that all your tables have a primary key which is a numeric column called ID then you can simplify the select statement. Contrariwise, if some of your primary keys are compound constraints you will need to handle that.

Obviously I plumped for generating sequences because they're simpler. Writing the more complex trigger implementation is left as an exercise for the reader :)

Answer 2

thanks for the script. I altered it a little bit and did the trigger implementation. Feel free to use it.

declare
    curr_mx number;
    counter number;
    seq_name varchar2 (30);
    trigger_name varchar2 (30);
begin
    for lrec in ( select ucc.table_name
                         , ucc.column_name
                  from user_constraints uc
                       join user_cons_columns ucc
                           on ucc.table_name = uc.table_name
                          and ucc.constraint_name = uc.constraint_name
                       join user_tab_columns utc
                          on utc.table_name = ucc.table_name
                          and utc.column_name = ucc.column_name
                  where uc.constraint_type = 'P' -- primary key
                  and   utc.data_type = 'NUMBER' -- only numeric columns
                  )
    loop
        execute immediate 'select (max ('|| lrec.column_name ||')+1) from ' ||lrec.table_name 
            into curr_mx;

        IF curr_mx is null THEN
            curr_mx := 0;
        END IF;

        IF counter is null THEN
            counter := 0;
        END IF;    

        /* check length of sequence name, 30 is max */
        IF length(lrec.table_name ||'_'|| lrec.column_name || '_SEQ') > 30 THEN

            IF length(lrec.column_name || '_SEQ') > 30 THEN

                seq_name := counter || '_PKA_SEQ';

            ELSE

                seq_name := lrec.column_name || '_SEQ';

            END IF;  

        ELSE

            seq_name := lrec.table_name ||'_'|| lrec.column_name || '_SEQ';

        END IF;

        /* check length of trigger name, 30 is max */
        IF length(lrec.table_name || '_PKA_T') > 30 THEN

            trigger_name := counter || '_PKA_T'; 

        ELSE

            trigger_name := lrec.table_name || '_PKA_T';

        END IF;        

        counter := counter +1;

        dbms_output.put_line(
          'CREATE SEQUENCE "' || seq_name || '"'
          ||' START WITH ' || to_char( curr_mx + 1 )  ||';'
        );
        dbms_output.put_line('/');
        dbms_output.put_line(
          'CREATE OR REPLACE TRIGGER "' || trigger_name || '"' 
          || ' BEFORE INSERT ON "' || lrec.table_name || '"' 
          || ' FOR EACH ROW '
          || ' BEGIN '
          || ' :new."' || lrec.column_name || '" := "' || seq_name || '".nextval;'
          || ' END;'
        );
        dbms_output.put_line('/');

    end loop;
end;

I also checked if the names of the sequences and triggers are longer than 30 characters because oracle won´t accept these.

EDIT: Had to put '/' after each line so you can execute all statements at one run.