Monday, July 30, 2007

Keeping History of PL/SQL code changes

-- Create a Table for logging the modifications
CREATE TABLE
source_hist

AS SELECT SYSDATE change_date ,user_source.*
FROM
user_source WHERE 1=2;

--Trigger for tracking the changes
CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON scott.schema -- Change SCOTT to your schema name
DECLARE
BEGIN
IF DICTIONARY_OBJ_TYPE
IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE') THEN

INSERT INTO source_hist
SELECT sysdate,user_source.*
FROM user_source

WHERE TYPE=DICTIONARY_OBJ_TYPE
AND NAME=DICTIONARY_OBJ_NAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,SQLERRM);
END;

No comments: