A Simple PLSQL Source Control Repository in Oracle

Create A Simple PL/SQL Source Control Repository in Oracle Using Trigger

1 Create a table as repository of pl/sql source code

create table CVS
(
  P_VER   NUMBER,
  P_TEXT  LONG,
  P_NAME  VARCHAR2(100),
  P_OWNER VARCHAR2(30),
  P_TYPE  VARCHAR2(30),
  P_DATE  DATE,
  P_USER  VARCHAR2(30)
)

 2 Create a sequence as version number generator

create sequence VER_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

 3 Create a trigger to store procedure code in CVS table after created

create or replace trigger CVS_PROCEDURE
  after create on schema 
declare
  P_TEXT VARCHAR2(10000);
begin
  
  IF(ora_dict_obj_type = 'PROCEDURE') THEN
  
    P_TEXT := 'CREATE OR REPLACE ';
    FOR RA IN (SELECT TEXT FROM USER_SOURCE 
                WHERE NAME=ora_dict_obj_name ORDER BY LINE)
    LOOP
        P_TEXT := P_TEXT || RA.TEXT;
    END LOOP;
    
    INSERT INTO CVS(P_OWNER,P_NAME,P_TYPE,P_TEXT,
                    P_VER,P_USER,P_DATE)
    SELECT ora_dict_obj_owner,
           ora_dict_obj_name, 
           ora_dict_obj_type,
           P_TEXT,
           VER_SEQ.NEXTVAL, USER, SYSDATE
    FROM dual;    
  
  END IF;       
end CVS_PROCEDURE;

 done ^_^

Every compilation of a  procedure will result in a new record in CVS table.