記錄日志(自治事務

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;