Hi Oracles,
I am a newbie, and I am trying to execute multiple column adds on various tables using a single plsql block such as the following, I was told that the way im handling the exeption is wrong because if the first column fails then the rest of the block will be ignored.
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;
/
and that it should be the following
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;
/
isnt there a better way to handle these exceptions without so much repetition? like a loop?