Rerunable alter-Scripts which catches the exceptions for deployment to avoid delta-deployment-scripts

In a lot of Projects I see a Testdatabase which gets the new Deployment and afterwards the developer have to make delta-deployments to get the testmachine to the most current version.
There are several solutions:

• The better one of course is to get always a fresh copy of the current-production-version to be able to deploy the new version the first time.
• A Solution can be to check for the last version and to deploy all necessary delta-deployments serial to get to the latest version automatically.
• Another Solution is to run the same scripts again and again and catch all expected exceptions

— create table
DECLARE OBJECTNAME_ALEREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (OBJECTNAME_ALEREADY_EXISTS, -00955); -- -00955: name is already being used by existing object
BEGIN
EXECUTE IMMEDIATE q'[create table [TABLENAME]
([ATTRNAME] [TYPE],
[ATTRNAME] [TYPE]
)]';
EXCEPTION WHEN OBJECTNAME_ALEREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: Objekt(name) already existing');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/


— drop column
DECLARE COLUMN_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (COLUMN_DOES_NOT_EXIST, -00904); --ORA-01430: ORA-00904: "DESCR": invalid identifier
BEGIN
EXECUTE IMMEDIATE 'alter table [table] drop [column] colname';
EXCEPTION WHEN COLUMN_DOES_NOT_EXIST THEN DBMS_OUTPUT.PUT_LINE('OK: column does not exist in table, already dropped?');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add column
DECLARE COLUMN_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (COLUMN_ALREADY_EXISTS, -01430); --ORA-01430: column being added already exists in table
BEGIN
EXECUTE IMMEDIATE 'alter table [table] add [column] [coltype]';
EXCEPTION WHEN COLUMN_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: column being added already exists in table');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— make column not null
DECLARE COLUMN_ALREADY_NOT_NULL EXCEPTION;
--ORA-01442: column to be modified to NOT NULL is already NOT NULL
PRAGMA EXCEPTION_INIT (COLUMN_ALREADY_NOT_NULL, -01442);
BEGIN
EXECUTE IMMEDIATE 'alter table [table] modify [column] not null';
EXCEPTION
WHEN COLUMN_ALREADY_NOT_NULL THEN DBMS_OUTPUT.PUT_LINE('OK: column to be modified to NOT NULL is already NOT NULL');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–Insert new row
BEGIN
insert into [table] ([column], [column]) values([colvalue1],[colvalue2]);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('OK: Row already inserted (DUP_VAL_ON_INDEX)');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add Constraint Unique Key
DECLARE UNIQUE_KEY_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (UNIQUE_KEY_ALREADY_EXISTS, -02261); -- -02261: such unique or primary key already exists in the table
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT [CONSTRAINT_NAME] UNIQUE ( [column])';
EXCEPTION WHEN UNIQUE_KEY_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add Unique Index
DECLARE UNIQUE_KEY_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (UNIQUE_KEY_ALREADY_EXISTS, -02261); -- -02261: such unique or primary key already exists in the table
NAME_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (NAME_ALREADY_EXISTS, -00955); -- -00955: name is already used by an existing object
BEGIN
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX UI_WFID ON TBGLOBALSTATUS (WFID)';
EXCEPTION WHEN UNIQUE_KEY_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN NAME_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

— add check constraint
DECLARE CONSTRAINT_NAME_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (CONSTRAINT_NAME_ALREADY_EXISTS, -02264); -- -02264: name already used by an existing constraint
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT CHECK ([column] IS NOT NULL) ENABLE';
EXCEPTION
WHEN CONSTRAINT_NAME_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: name already used by an existing constraint');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–add REF-CONSTRAINT / FOREIGN KEY
DECLARE REFCONSTRAINT_ALEREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (REFCONSTRAINT_ALEREADY_EXISTS, -02275); -- -02275: such a referential constraint already exists in the table
PARENT_KEY_NOT_FOUND_1 EXCEPTION;
PRAGMA EXCEPTION_INIT (PARENT_KEY_NOT_FOUND_1, -02291); -- -02291: integrity constraint (string.string) violated - parent key not found
PARENT_KEY_NOT_FOUND_8 EXCEPTION;
PRAGMA EXCEPTION_INIT (PARENT_KEY_NOT_FOUND_8, -02298); -- -02298: cannot validate (string.string) - parent keys not found
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT FOREIGN KEY ( [columns] ) REFERENCES

— create INDEX
DECLARE NAME_ALREADY_USED EXCEPTION;
PRAGMA EXCEPTION_INIT (NAME_ALREADY_USED, -00955); -- -00955: name is already used by an existing object
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX [INDEX] ON [TABLE]([column]) ';
EXCEPTION WHEN NAME_ALREADY_USED THEN DBMS_OUTPUT.PUT_LINE('OK: name is already used by an existing object');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–Insert new Job
DECLARE
v_job_id number := 99;
v_jobtask VARCHAR2(500) := 'myschema.PKG_MYPACKAGE.myprocedure(p_mypara => ''NO'');';
v_next_date date := trunc(SYSDATE)+1 + 1/24; /* 01:00*/
v_interval varchar2(50) := 'SYSDATE + 1 /* daily */';
v_count number;
BEGIN
select count(*)
into v_count
from user_jobs
where what = v_jobtask
or job = v_job_id;

if v_count = 0 then

DBMS_JOB.isubmit (
job => v_job_id,
what => v_jobtask,
next_date => v_next_date,
interval => v_interval
);
COMMIT;
dbms_output.put_line('job '||v_job_id||': '||v_jobtask||' inserted and commited!');
else
DBMS_JOB.change (
job => v_job_id,
what => v_jobtask,
next_date => v_next_date,
interval => v_interval
);
COMMIT;
bms_output.put_line('job '||v_job_id||': '||v_jobtask||' changed already inserted job!');
end if;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
/


Published on: Jul 4, 2014 @ 9:08
Updated on: Nov 20, 2014 @ 9:48
Updated on: Nov 26, 2014 @ 12:31
Updated on: Dec 02, 2014 @ 14:48
Updated on: Dec 09, 2014 @ 17:12