Thursday, August 27, 2009

Oracle PL/SQL Version Controlling

--Firstly, create a table to store change history


CREATE TABLE DBChangeHistory AS
SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
FROM ALL_SOURCE
WHERE 1=2;

--Secondly, create a trigger to store the code when it's changed
--You can modify this code to store Version number
CREATE OR REPLACE TRIGGER tgTrackDBChange
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')
THEN
INSERT INTO DBChangeHistory
SELECT sysdate, all_source.*
FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE
AND NAME = ORA_DICT_OBJ_NAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
--Raise error here;
END;

No comments:

Post a Comment