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;
        /

MT0 · Accepted Answer · 2022-06-17 21:06:58Z

1

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;
/

answered Jun 17, 2022 at 21:06

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Garcia Over a year ago

Thanks for coming back, I've added an update, could you let me know if thats also then incorrect?

MT0 Over a year ago

@DavidGarcia Same as the answer. If you want to catch when the first statement fails and then still run the second statement to create the table then you need to wrap each statement in their own anonymous block with their own exception handler. If you do not then as soon as one statement fails then all the following statements will never be run.

William Robertson · Accepted Answer · 2022-06-19 08:37:01Z

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.

Collectives™ on Stack Overflow

Oracle: execute immediate multiple statements

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related