PROCEDURE P_NewSysLog --记录日志(自治事务)
(
v_loglevel IN syslog.loglevel%TYPE, --日志级别
v_opuser IN syslog.opuser%TYPE, --操作人
v_opproc IN syslog.opproc%TYPE, --涉及存储过程
v_opcomm IN syslog.opcomm%TYPE, --操作说明
v_opdone IN syslog.opdone%TYPE, --操作结果:T,成功;F,失败;N,操作不涉及成功失败;
v_opresult IN syslog.opresult%TYPE --详细操作结果
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_logstate syslogstate.logstate%TYPE;
BEGIN
SET TRANSACTION NAME 'P_NewSysLog';
SELECT t.logstate
INTO v_logstate --读取系统当前是否允许记录本类日志
FROM syslogstate t
WHERE t.loglevel = v_loglevel;
IF v_logstate = 'ON' --当syslogstate.logstate为ON时,才记录日志
THEN
INSERT INTO syslog
(loglevel,
opuser,
opdate,
opproc,
opcomm,
opdone,
opresult)
VALUES
(v_loglevel,
v_opuser,
SYSTIMESTAMP,
v_opproc,
v_opcomm,
v_opdone,
v_opresult);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END P_NewSysLog;
/**********************************************************************************
写文件前的操作
CREATE OR REPLACE DIRECTORY LOGDIR AS 'C:\'; --1:建立一个ORACLE的目录对象,比如C:\.
GRANT READ, WRITE ON DIRECTORY LOGDIR TO 用户; --2:对这个目录对象进行授权
**********************************************************************************/
PROCEDURE P_WriteOSFile(v_Msg IN NVARCHAR2) --写入服务器文件
IS
v_file utl_file.file_type;
BEGIN
v_file := utl_file.fopen('LOGDIR', 'TLSYS.LOG', 'A');
utl_file.put_line(v_file,
'****************************Begin ' ||
to_char(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss.ff3') ||
'****************************'); --写入开始标志
utl_file.put_line(v_file,
'OS User:[' || SYS_CONTEXT('USERENV', 'OS_USER') || ']'); --写入OS_USER
utl_file.put_line(v_file,
'Terminal:[' || SYS_CONTEXT('USERENV', 'TERMINAL') || ']'); --写入Terminal
utl_file.put_line(v_file,
'Host:[' || SYS_CONTEXT('USERENV', 'HOST') || ']'); --写入HOST
utl_file.put_line(v_file,
'IP Address:[' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ']'); --写入IP_ADDRESS
utl_file.put_line(v_file,
'DB Name:[' || SYS_CONTEXT('USERENV', 'DB_NAME') || ']'); --写入DB_NAME
utl_file.put_line(v_file, 'SID:[' || SYS_CONTEXT('USERENV', 'SID') || ']'); --写入SID
utl_file.put_line(v_file,
'SessionID:[' || SYS_CONTEXT('USERENV', 'SESSIONID') || ']'); --写入SessionID
utl_file.put_line(v_file,
'Current User:[' ||
SYS_CONTEXT('USERENV', 'CURRENT_USER') || ']'); --写入CURRENT_USER
utl_file.put_line(v_file,
'Session User:[' ||
SYS_CONTEXT('USERENV', 'SESSION_USER') || ']'); --写入SESSION_USER
utl_file.put_line(v_file, 'Msg:[' || v_Msg || ']'); --写入自定义信息
utl_file.put_line(v_file,
'*****************************End ' ||
to_char(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss.ff3') ||
'*****************************'); --写入结束标志
utl_file.put_line(v_file, ' '); --写入空行
utl_file.fflush(v_file); --刷缓冲
utl_file.fclose(v_file); --关闭文件指针
EXCEPTION
WHEN OTHERS THEN
NULL;
END P_WriteOSFile;