Sunday, February 7, 2010

How to keep Source code creation History

How to keep Source code creation History
==========================================

CREATE TABLE halim.SOURCE_HIST
(
CHANGE_DATE DATE NULL,
NAME VARCHAR2(30 BYTE) NULL,
TYPE VARCHAR2(12 BYTE) NULL,
LINE NUMBER NULL,
TEXT VARCHAR2(4000 BYTE) NULL
)


CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON halim.SCHEMA
DECLARE
BEGIN
IF ora_dict_obj_type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
THEN
INSERT INTO source_hist
SELECT SYSDATE, user_source.*
FROM user_source
WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/


SELECT DISTINCT change_date, NAME, TYPE
FROM source_hist
ORDER BY change_date DESC

No comments: