Keep history of Oracle Source code in DB

You can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger. This will allow you to easily revert to previous code if required.


PL/SQL Code History
1- Make history table with some additional info
CREATE TABLE HOMELOG.HOME_SOURCE_HIST
(
  CHANGE_DATE       DATE,
  SCN NUMBER,
  ORA_DICT_OBJ_NAME  VARCHAR2(30 BYTE),
  ORA_LOGIN_USER    VARCHAR2(30 BYTE),
  ORA_INSTANCE_NUM  NUMBER,
  IP_ADDRESS        VARCHAR2(30 BYTE),
  HOSTNAME          VARCHAR2(30 BYTE),
  INSTANCE_NAME     VARCHAR2(30 BYTE),
  OS_USER           VARCHAR2(30 BYTE),
  SERVICE_NAME      VARCHAR2(30 BYTE),
  TERMINAL          VARCHAR2(30 BYTE),
  OWNER             VARCHAR2(30 BYTE),
  NAME              VARCHAR2(30 BYTE),
  TYPE              VARCHAR2(12 BYTE),
  LINE              NUMBER,
  TEXT              VARCHAR2(4000 BYTE)
)
2- Write the trigger
CREATE OR REPLACE TRIGGER home_code_change_hist        -- Store code in hist table
--AFTER CREATE ON HOME.SCHEMA          -
AFTER CREATE ON DATABASE
DECLARE
BEGIN
  IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE',   'PACKAGE BODY', 'TYPE',      'TYPE BODY')  and ora_dict_obj_owner IN('HOME','') THEN
     -- Store old code in SOURCE_HIST table
     INSERT INTO HOMELOG.HOME_SOURCE_HIST  
            SELECT sysdate,dbms_flashback.get_system_change_number,ORA_DICT_OBJ_NAME,ORA_LOGIN_USER,ORA_INSTANCE_NUM,sys_context('USERENV', 'IP_ADDRESS'),sys_context('USERENV', 'HOST'),sys_context('USERENV', 'INSTANCE_NAME'),
            sys_context('USERENV', 'OS_USER'),sys_context('USERENV', 'SERVICE_NAME'),sys_context('USERENV', 'TERMINAL'),       all_source.* FROM ALL_SOURCE
             WHERE  TYPE = ORA_DICT_OBJ_TYPE  -- DICTIONARY_OBJ_TYPE
               AND  NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME
  END IF;
EXCEPTION
  WHEN OTHERS THEN
       raise_application_error(-20000, SQLERRM);
END;
3- do some activity to check the trigger working

 CREATE OR REPLACE procedure HOME.t1
is
begin
null;
--new  at 10:10
end;
4- Query the table to view the history, you can create the view for easiness
create or replace view av_home_code_change_hist as
select distinct change_date,scn,ORA_DICT_OBJ_NAME,ORA_LOGIN_USER,IP_ADDRESS,HOSTNAME,OS_USER,TERMINAL
from HOMELOG.HOME_SOURCE_HIST
order by scn desc

5- Additionaly you can create a trigger on history table to avoid update and delete.
create or replace trigger trg_HOMELOG_HOME_SOURCE_HIST
before delete or update
on HOMELOG.HOME_SOURCE_HIST
begin
 raise_application_error(-20000, 'No deletion/updation allowed to this table');
end;

Views Code History
The same can be done for the views code also.

1- Create history table

CREATE TABLE HOMELOG.HOME_VIEW_SOURCE_HIST
(
CHANGE_DATE DATE,
SCN NUMBER,
ORA_DICT_OBJ_NAME VARCHAR2(30 BYTE),
ORA_LOGIN_USER VARCHAR2(30 BYTE),
ORA_INSTANCE_NUM NUMBER,
IP_ADDRESS VARCHAR2(30 BYTE),
HOSTNAME VARCHAR2(30 BYTE),
INSTANCE_NAME VARCHAR2(30 BYTE),
OS_USER VARCHAR2(30 BYTE),
SERVICE_NAME VARCHAR2(30 BYTE),
TERMINAL VARCHAR2(30 BYTE),
OWNER VARCHAR2(30 BYTE),
TEXT_LENGTH NUMBER,
TEXT CLOB
)2- Create the trigger to be fired when view is created

CREATE OR REPLACE TRIGGER home_view_change_hist -- Store code in hist table
--AFTER CREATE ON HOME.SCHEMA -
AFTER CREATE ON DATABASE
DECLARE
BEGIN

IF ORA_DICT_OBJ_TYPE in ('VIEW') and ora_dict_obj_owner IN('HOME','') THEN
--INSERT INTO HOMELOG.HOME_VIEW_SOURCE_HIST (change_date,scn) values (sysdate,dbms_flashback.get_system_change_number);
-- Store old code in SOURCE_HIST table
INSERT INTO HOMELOG.HOME_VIEW_SOURCE_HIST
SELECT sysdate,dbms_flashback.get_system_change_number,ORA_DICT_OBJ_NAME,ORA_LOGIN_USER,ORA_INSTANCE_NUM,sys_context('USERENV', 'IP_ADDRESS'),sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'INSTANCE_NAME'), sys_context('USERENV', 'OS_USER'),sys_context('USERENV', 'SERVICE_NAME'),sys_context('USERENV', 'TERMINAL'),
all_views.OWNER,all_views.TEXT_LENGTH,to_lob(all_views.TEXT)
FROM ALL_VIEWS
WHERE OWNER = ora_dict_obj_owner -- DICTIONARY_OBJ_TYPE IN 8i
AND VIEW_NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i

END IF;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;3- Do some stuff to check the working
create or replace view home.v1 as select * from dual
create or replace view home.v1 as select sysdate dt,'First Change' change from dual
create or replace view home.v1 as select sysdate dt,'Second Change' change from dual
4- Check the history table (you can create view also)
create or replace view av_home_VIEW_change_hist as
select change_date,scn,ORA_DICT_OBJ_NAME,ORA_LOGIN_USER,IP_ADDRESS,HOSTNAME,OS_USER,TERMINAL,OWNER,TEXT_LENGTH,TEXT
from HOMELOG.HOME_view_SOURCE_HIST
order by scn desc
5- Optionally restrict for deletion and updation 
create or replace trigger trg_HOMELOG_HOME_VW_SRC_HIST
before delete or update
on HOMELOG.HOME_VIEW_SOURCE_HIST
begin
raise_application_error(-20000, 'No deletion/updation allowed to this table');
end;