一、背景
在日常编写oracel存储过程中需要用到记录日志文件的情况,比如某个存储过程处理数据量比较大,我们需要实时观察存储过程处理情况,可以通过记录日志文件方式进行跟踪。
下面通过ORACLE提供的utl_file来实现日志记录。
二、操作步骤
1.创建oracle用户,并授予dba权限。
创建oracle用户
create user test identified by test;
授予DBA权限
grant connect,resource,dba to test;
2.创建日志目录
sqlplus / as sysdba<
create directory LOGDIR as '/home/db/oracle/log'; --此处目录可自行指定
grant read,write on directory LOGDIR to test; --用户test可对目录进行读写
grant execute on utl_file to test;
!
3.编写日志存储过程包
---------------------------------------------------------------------------
-- 文件名: log.sql
-- 包 名: wlog
-- 包方法: enable( flag ) 控制是否记录日志(true,false),默认为true
-- init(logfile,procname) 日志初始化,
-- logfile日志文件名
-- procname存储过程名
-- write( msg ) 记录日志,参数为日志内容
-- put_line( msg) 记录日志,参数为日志内容
-- stop(flag) 结束日志记录,flag: 0成功完成 ,其他失败完成
---------------------------------------------------------------------------
create or replace package wlog is
g_flag boolean := true;
g_log_flag boolean := true;
g_file UTL_FILE.FILE_TYPE;
g_logfile varchar(256);
g_procname varchar(50);
g_old_procname varchar(50) ;
procedure enable(p_flag boolean default true );
procedure init(p_logfile varchar,p_procname varchar );
procedure write(p_logline varchar);
procedure log(p_fname varchar2,p_info varchar2);
procedure put_line(p_logline varchar);
procedure stop(p_flag char default '0');
end;
/
show err
create or replace package body wlog is
procedure enable( p_flag boolean default true)
is
begin
g_flag := p_flag;
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm || dbms_utility.format_error_backtrace );
end;
procedure setlogflag( p_flag boolean default true)
is
begin
g_log_flag := p_flag;
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm || dbms_utility.format_error_backtrace );
end;
procedure init( p_logfile varchar,p_procname varchar )
is
v_datetime varchar(50);
begin
if g_flag = false then
return ;
end if;
g_logfile := p_logfile;
g_procname := p_procname;
g_old_procname := p_procname;
g_file := utl_file.fopen( 'LOGDIR' , g_logfile , 'a');
utl_file.put_line(g_file, '');
select to_char(current_timestamp(3),'YYYY-MM-DD HH24:MI:SSxFF')
into v_datetime from dual;
utl_file.put_line(g_file, v_datetime || ' PROCEDURE NAME[' || g_procname || '] Execute Begin...');
utl_file.put_line(g_file, '-----------------------------------------------------');
utl_file.fflush(g_file);
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm ||dbms_utility.format_error_backtrace );
end;
procedure write_core(p_logline varchar,p_callme varchar ,p_lineno number)
is
v_file_exist boolean;
v_file_length number(10,2);
v_block_size binary_integer;
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number ;
l_type varchar(30);
v_datetime varchar(50);
begin
if g_flag = false then
return ;
end if;
utl_file.fgetattr('LOGDIR',g_logfile,v_file_exist,v_file_length,v_block_size );
if NOT v_file_exist then
if utl_file.is_open(g_file) then
utl_file.fclose( g_file );
end if;
g_file := utl_file.fopen('LOGDIR' ,g_logfile, 'a' );
end if ;
--owa_util.who_called_me(l_owner,l_name,l_lineno,l_type );
l_name := p_callme;
l_lineno :=p_lineno;
select to_char(current_timestamp(3),'YYYY-MM-DD HH24:MI:SSxFF')
into v_datetime from dual;
if l_name is null then
UTL_FILE.put_line(g_file, l_lineno || ':' || p_logline );
else
if g_old_procname = l_name then
UTL_FILE.put_line(g_file, l_lineno || ':' || p_logline );
else
if l_name = 'LOG' then
NULL;
else
g_old_procname:= l_name ;
end if;
UTL_FILE.put_line(g_file,v_datetime || ' Enter ' || l_name );
UTL_FILE.put_line(g_file, l_lineno || ':' || p_logline );
end if;
end if;
utl_file.fflush(g_file);
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm ||dbms_utility.format_error_backtrace );
end;
procedure write(p_logline varchar)
is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number ;
l_type varchar(30);
begin
if g_flag = false then
return ;
end if;
owa_util.who_called_me(l_owner,l_name,l_lineno,l_type );
write_core( p_logline ,l_name,l_lineno);
end;
procedure put_line(p_logline varchar)
is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number ;
l_type varchar(30);
begin
if g_flag = false then
return ;
end if;
owa_util.who_called_me(l_owner,l_name,l_lineno,l_type );
write_core( p_logline ,l_name,l_lineno);
end;
procedure stop(p_flag char default '0' )
is
v_datetime varchar(50);
v_file_exist boolean;
v_file_length number(10,2);
v_block_size binary_integer;
begin
if g_flag = false then
return ;
end if;
utl_file.fgetattr('LOGDIR',g_logfile,v_file_exist,v_file_length,v_block_size );
if NOT v_file_exist then
if utl_file.is_open(g_file) then
utl_file.fclose( g_file );
end if ;
g_file := utl_file.fopen('LOGDIR' ,g_logfile, 'a' );
end if ;
select to_char(current_timestamp(3),'YYYY-MM-DD HH24:MI:SSxFF')
into v_datetime from dual;
utl_file.put_line(g_file, '----------------------------------------------------------');
if p_flag = '0' then
utl_file.put_line(g_file, v_datetime || ' PROCEDURE NAME[' || g_procname || '] success.');
else
utl_file.put_line(g_file, v_datetime || ' PROCEDURE NAME[' || g_procname || '] fail.');
end if;
utl_file.fflush(g_file);
utl_file.fclose(g_file );
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm ||dbms_utility.format_error_backtrace);
end;
--记录日志过程
procedure log(p_fname varchar2,p_info varchar2 )
is
l_file UTL_FILE.FILE_TYPE;
begin
l_file := utl_file.fopen('LOGDIR' ,p_fname, 'a' );
utl_file.put_line(l_file, p_info );
utl_file.fflush(l_file);
utl_file.fclose(l_file );
exception
when others then
dbms_output.put_line('ERROR:' || sqlcode || ':' || sqlerrm ||dbms_utility.format_error_backtrace);
end;
end;
/
--查看创建出错信息
show err
到此,记录日志存储过程包已创建,下面看看具体应用。
三、实例
创建两个测试存储过程
create or replace procedure test1
is
begin
wlog.write('test1....');
end;
/
show error
create or replace procedure test
is
begin
wlog.write('log test.....');
test1();
end;
/
show error
使用方式一:
exec wlog.init('test.log','TEST');
exec test();
exec wlog.stop();
使用方式二:
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
exec wlog.log('test1.log','TEST........');
方式一可以查看存储过程相互调用关系及日志记录,方式二只是线型的记录每一条日志内容。