OraclePKG日志管理 oracle pkg

我们经常会写很多pkg，procedures等过程。这些过程不止在程序中用到，并且也会提供给运营人员手动执行。由于手动执行是不可预知的，所以我们需要记录下谁进行了这些操作，以便有数据问题出现时可以进行跟踪。

首先我们建立一张日志表，用于记录pkg的执行情况。

create table PKG_RUN_LOG
(
SEQNUMBER not null,
SESSION_USER VARCHAR2(100),
OS_USERVARCHAR2(100),
IP_ADDRESSVARCHAR2(20),
PKG_NAMEVARCHAR2(200) not null,
START_TIMEDATE not null,
END_TIMEDATE
)

然后建立一个SEQ，用于日志表的SEQ：

-- Create sequence
create sequence LOG_SEQ
minvalue 100000000000001
maxvalue 999999999999999
start with 100000000000021
increment by 1
cache 20
order;

然后建立我们所需的pkg：

create or replace package LOG_PKG is

procedure Write_pkg_start(v_pkg_name inpkg_run_log.pkg_name%type,
v_seq_no outpkg_run_log.seq%type);

procedure Write_pkg_end(v_seq_no inpkg_run_log.seq%type);

procedureGet_User(v_seq_noout pkg_run_log.seq%type,
v_session_user out pkg_run_log.session_user%type,
os_userout pkg_run_log.os_user%type,
v_ip_address outpkg_run_log.ip_address%type);

end LOG_PKG;

create or replace package body LOG_PKG is
procedure Write_pkg_start(v_pkg_namepkg_run_log.pkg_name%type,
v_seq_no outpkg_run_log.seq%type) as
pragmaautonomous_transaction;--自治事务
v_session_user pkg_run_log.session_user%type;
os_userpkg_run_log.os_user%type;
v_ip_addresspkg_run_log.ip_address%type;
begin
Get_User(v_seq_no=> v_seq_no,
v_session_user => v_session_user,
os_user=> os_user,
v_ip_address =>v_ip_address);

insert intopkg_run_log
(seq,
session_user,
os_user,
ip_address,
pkg_name,
start_time,
end_time)
values
(v_seq_no,
v_session_user,
os_user,
v_ip_address,
v_pkg_name,
sysdate,
null);
commit;

exception
when othersthen
rollback;
end Write_pkg_start;

procedure Write_pkg_end(v_seq_no inpkg_run_log.seq%type) as
pragmaautonomous_transaction;--自治事务
begin
updatepkg_run_log t set t.end_time = sysdate where t.seq =v_seq_no;
commit;

exception
when othersthen
rollback;
end Write_pkg_end;

procedureGet_User(v_seq_noout pkg_run_log.seq%type,
v_session_user out pkg_run_log.session_user%type,
os_userout pkg_run_log.os_user%type,
v_ip_address outpkg_run_log.ip_address%type) as
begin
v_session_user := sys_context('USERENV', 'SESSION_USER');
os_user:= sys_context('USERENV', 'OS_USER');
v_ip_address :=sys_context('USERENV', 'IP_ADDRESS');

selectlog_seq.nextval into v_seq_no from dual;
end Get_User;

end LOG_PKG;

注意这里需要把pkg的过程定义为“自治事务”。

最后，让我们来测试一下：

declare
v_seq number := null;

begin

LOG_PKG.Write_pkg_start(v_pkg_name=> 'abcdefg',v_seq_no =>v_seq);
...执行你的具体业务逻辑的过程、pkg
log_pkg.Write_pkg_end(v_seq);

end;

查询表，可以看到如下记录：

爱华网本文地址 » http://www.aihuau.com/a/25101014/200153.html