Oracle: execute immediate multiple statements

Question

I need to create columns on multiple tables as well as indexes, I need to catch first if columns already exist as to ignore for the multiple executes, is the following the correct way of handling a single exeption for multiple columns adds executes?

----------------------------------
-- Alter column null --
----------------------------------
DECLARE
   col_allready_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(col_allready_exists, -01430);
BEGIN
   execute immediate 'ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0';
   execute immediate 'ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0';
   execute immediate 'ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0';
   execute immediate 'ALTER TABLE XtkRights ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
   execute immediate 'ALTER TABLE NmsSeedMember ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
   execute immediate 'ALTER TABLE NmsTrackingUrl ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';       
EXCEPTION
   WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
END;
/
    
----------------------------------
-- Index creation --
----------------------------------
DECLARE
    already_exists EXCEPTION;
    columns_indexed EXCEPTION;
    PRAGMA EXCEPTION_INIT ( already_exists, -955 );
    PRAGMA EXCEPTION_INIT (columns_indexed, -1408);
BEGIN
    EXECUTE IMMEDIATE 'CREATE INDEX XTKRIGHTS_TSLASTMODIFIED_IDX ON XTKRIGHTS(tsLastModified)';
    EXECUTE IMMEDIATE 'CREATE INDEX ER_TSLASTMODIFIED_IDX_CC057ED6 ON NmsSeedMember(tsLastModified)';
    EXECUTE IMMEDIATE 'CREATE INDEX RL_TSLASTMODIFIED_IDX_E5F04BF5 ON NmsTrackingUrl(tsLastModified)';
EXCEPTION
    WHEN already_exists OR columns_indexed THEN
        dbms_output.put_line('Index already exists, skipping...');
END;
/   

Update

Is the following also correct?

        DECLARE
           allready_null EXCEPTION;
           object_allready_exists EXCEPTION;
           PRAGMA EXCEPTION_INIT(allready_null, -01442);     
           PRAGMA EXCEPTION_INIT(object_allready_exists, -955);        
        BEGIN
           execute immediate 'ALTER TABLE NMSACTIVECONTACT MODIFY (ISOURCEID NULL)';
           execute immediate 'CREATE TABLE NMSACTIVECONTACT_CPY as SELECT * FROM NMSACTIVECONTACT where 1=0';
        EXCEPTION
           WHEN allready_null THEN
              dbms_output.put_line('ISOURCEID is already null, skipping...');
           WHEN object_allready_exists THEN
              dbms_output.put_line('NMSACTIVECONTACT_CPY already exists, continuing...');
        END;
        /

Answer 1

I normally put the DDL and exception handling into a procedure and call that for each column to be added:

declare
    procedure add_column
        ( p_table_name   user_tab_columns.table_name%type
        , p_column_name  user_tab_columns.column_name%type
        , p_column_spec  varchar2 )
    as
        column_already_exists exception;
        pragma exception_init(column_already_exists, -1430);
        k_ddl constant long :=
            'alter table '||p_table_name||' add '||p_column_name||' '||p_column_spec;
    begin
        execute immediate k_ddl;
        dbms_output.put_line(p_table_name||'.'||p_column_name||' added');
    exception
        when column_already_exists then
            dbms_output.put_line(p_table_name||'.'||p_column_name||' already exists, skipping...');
        when others then
            raise_application_error(-20000, 'Command failed: '||k_ddl, true);
    end;
begin
    add_column('XtkEnumValue',  'iOrder',        'number(20) default 0');
    add_column('XtkReport',     'iDisabled',     'number(3) default 0');
    add_column('XtkWorkflow',   'iDisabled',     'number(3) default 0');
    add_column('XtkRights',     'tsLastModified','timestamp(6) with time zone');
    add_column('NmsSeedMember', 'tsLastModified','timestamp(6) with time zone');
    add_column('NmsTrackingUrl','tsLastModified','timestamp(6) with time zone');       
end;

You could use similar logic for modifying columns, adding indexes etc.

If I was adding the same columns to multiple tables, I might construct a cursor For loop using a Cartesian product to generate all the table/column combinations and and a minus to subtract the ones that already exist in user_tab_columns.

Answer 2

No, catch the exception for each column. If you don't then it may fail on the first column and then it will skip all the remaining statements as the exception handling block is processed.

DECLARE
   col_allready_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(col_allready_exists, -01430);
BEGIN
  BEGIN
    execute immediate 'ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0';
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
  BEGIN
    execute immediate 'ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0';
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
  BEGIN
    execute immediate 'ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0';
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
  BEGIN
    execute immediate 'ALTER TABLE XtkRights ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
  BEGIN
    execute immediate 'ALTER TABLE NmsSeedMember ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
  BEGIN
    execute immediate 'ALTER TABLE NmsTrackingUrl ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';       
  EXCEPTION
    WHEN col_allready_exists THEN
      dbms_output.put_line('Already exists, skipping...');
  END;
END;
/