Oracle: execute immediate multiple statements-CodePudding

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

CodePudding user response：

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