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 ( [column] )';
EXCEPTION WHEN REFCONSTRAINT_ALEREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: REF-Constraint (FK) already exists');
WHEN PARENT_KEY_NOT_FOUND_1 OR PARENT_KEY_NOT_FOUND_8 THEN
DBMS_OUTPUT.PUT_LINE('Not able to create foreign-key-constraint: 02291: parent key not found. Following rows in parent missing:');
FOR currow in ( select [columns] from [table] minus select [columns] from [table]) --child minus parent
LOOP
dbms_output.put_line('xml_blobno: '||currow.[columns]);
END LOOP;
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
— 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
Compiling Objects When to use a slash ‚/‘
There was always a diffuse toppic for me in sqlscripts for sqlplus:
When to use a forwardslash (‚/‘) after a code-block and when not.
A simple marker:
Use a slash after every PL/SQL -block:
– Package
– Procedure
– Function
Example:
create or replace procedure procname as
null;
end procname;
/
Use NO slash after all DML -blocks:
– Table
– Index
– Constraint
– Sequence
– View
– Materialized View
– …
If there is an ‚Create or replace‘ in the first line of the code to compile there is no problem, but the object will be recreated unnecessarily.
But in the case of an index for example there will be an exception.
CASE-Ausdruck
Gibt einen Wert zurück/returns a value
set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
myresult :=
case
when myid=null then 'true'
when myid is null then 'is null'
else 'false'
end;
dbms_output.put_line(myresult);
END;
/
CASE-Anweisung
Führt eine Aktion aus / Processes an action
set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
case
when myid=null then
myresult :='true';
when myid is null then
myresult :='is null';
else myresult :='false';
end case;
dbms_output.put_line(myresult);
END;
/
CREATE OR REPLACE PROCEDURE COPY_EMPS(limit_in IN PLS_INTEGER DEFAULT 100) is
-- EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('emp_bulk_test', 'emp_bulk_errors');
-- create table emp_bulk_test as select * from employees;
-- truncate table emp_bulk_test;
CURSOR employees_cur
IS
SELECT * FROM employees;
TYPE employees_rec IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_rec employees_rec;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_rec LIMIT limit_in;
FOR indx IN 1 .. l_rec.COUNT
LOOP
insert into emp_bulk_test
(employee_id
,first_name
,last_name
,email
,phone_number
,hire_date
,job_id
,salary
,commission_pct
,manager_id
,department_id)
VALUES (l_rec(indx).employee_id
,l_rec(indx).first_name
,l_rec(indx).last_name
,l_rec(indx).email
,l_rec(indx).phone_number
,l_rec(indx).hire_date
,l_rec(indx).job_id
,l_rec(indx).salary
,l_rec(indx).commission_pct
,l_rec(indx).manager_id
,l_rec(indx).department_id)
LOG ERRORS INTO emp_bulk_errors
;
END LOOP;
commit;
dbms_output.put_line('commit!');
EXIT WHEN l_rec.COUNT < limit_in;
END LOOP;
CLOSE employees_cur;
end copy_emps;
/
Eine weitere WordPress-Website
