oracle 通过触发器记录各个版本存储过程

oracle存储过程在开发的过程中会有多人修改，会导致pl/sql版本冲突，通过该操作可以记录各个版本的pl/sql记录，追溯历史的变更

1. 创建表记录各个版本信息(ODDS_SOURCELOG.sql)

-- Create table
create table ODDS_SOURCELOG
(
 opdate        DATE not null,
 object_name   VARCHAR2(128) not null,
 object_type   VARCHAR2(20) not null,
 last_content  CLOB,
 created       DATE,
 last_dll_time DATE,
 session_user  VARCHAR2(128) not null,
 os_user       VARCHAR2(128),
 owner         VARCHAR2(128),
 backver       NUMBER(16),
 ip_address    VARCHAR2(128)
);
-- Add comments to the table
comment on table ODDS_SOURCELOG  is '函数过程日志，通用';
-- Add comments to the columns
comment on column ODDS_SOURCELOG.opdate  is '操作时间';
comment on column ODDS_SOURCELOG.object_name  is '对象名';
comment on column ODDS_SOURCELOG.object_type  is '对象类型';
comment on column ODDS_SOURCELOG.last_content  is '最近内容';
comment on column ODDS_SOURCELOG.created  is '建立时间';
comment on column ODDS_SOURCELOG.last_dll_time  is '最近执行时间';
comment on column ODDS_SOURCELOG.session_user  is '操作oracle登录用户';
comment on column ODDS_SOURCELOG.os_user  is '操作的电脑名';
comment on column ODDS_SOURCELOG.owner  is ' 所有者';
comment on column ODDS_SOURCELOG.backver  is '流水号';
comment on column ODDS_SOURCELOG.ip_address is '操作的IP地址';
-- Create/Recreate indexes
create index IDX_ODDS_SOURCELOG_OPDATE on ODDS_SOURCELOG (OPDATE);

2. 操作日志记录(ODTAC_SOURCELOG.trg)

CREATE OR REPLACE TRIGGER ODTAC_SOURCELOG
 after create or drop on database
declare
 as_out clob := null;
 --本触发器用于记录存储过程的编译前的信息
begin
 if ora_dict_obj_type in ('FUNCTION', 'PACKAGE BODY', 'PACKAGE', 'PROCEDURE') then
   dbms_lob.createtemporary(as_out,true);
   for cur in (select text
                 from dba_source
                where type = ora_dict_obj_type
                  and owner = ora_dict_obj_owner
                  and name = ora_dict_obj_name) loop
     dbms_lob.append(as_out, cur.text);
   end loop;
   --插入版本信息
   --select sb_prseno.nextval into ln_backver from dual;
   insert into ODDS_SOURCELOG
     (BACKVER,
      OPDATE,
      OBJECT_NAME,
      OBJECT_TYPE,
      LAST_CONTENT,
      CREATED,
      LAST_DLL_TIME,
      SESSION_USER,
      OS_USER,
      IP_ADDRESS,
      OWNER)
   select to_char(sysdate,'yyyymmddhhmiss') as BACKVER,
          sysdate as OPDATE,
          object_name as OBJECT_NAME,
          object_type as OBJECT_TYPE,
          as_out as CONTENT,
          created as CREATED,
          last_ddl_time LAST_DLL_TIME,
          sys_context('userenv', 'session_user') as SESSION_USER,
          sys_context('userenv', 'host') as OS_USER,
          sys_context('userenv', 'ip_address') as IP_ADDRESS,
          ora_dict_obj_owner
     from dba_objects
    where object_name = ora_dict_obj_name
      and owner = ora_dict_obj_owner
      and object_type = ora_dict_obj_type;
 end if;
end;

---

操作日志版触发器

1. 首先以sys权限执行以下

grant select on dba_source  to 用户对象A ;
grant select on dba_objects to用户对象A ;
如有dba 权限也可以执行grant select any  dictionary to 用户对象A;

2. 然后在 用户对象A 下面执行

ODDS_SOURCELOG.sql

3. 执行成功以后执行

ODTAC_SOURCELOG.trg

以后每次 ‘FUNCTION’, ‘PACKAGE BODY’, ‘PACKAGE’, ‘PROCEDURE’ 就可以历史版本记录了