實時監控儲存過程中應用的日誌資訊

       幾年前開發人員問我，要怎樣解決儲存過程在執行過程中，能實時看到日誌生

成，因為無論採用DBMS_OUTPUT還是dbms_system.ksdwrt，只能在儲存過程執

行完成之後才生成日誌資訊。這個問題一直困擾了我多年，但很幸運能看到一位韓

國的資料庫專家Dion Cho部落格中提供的指令碼，解決了這個困擾多年的問題。

以下具體的指令碼：

1、建立包

create or replace package pkg_log
    as
      type log_array is table of varchar2(4000);
        procedure log(message in varchar2);
        procedure flush;
        function get_log return log_array pipelined;
    end;
    /

create or replace package body pkg_log
    as
        procedure log(message in varchar2) is
            v_status number;
        begin
            dbms_pipe.pack_message(sysdate || ', ' || message);
        v_status := dbms_pipe.send_message('log');
        end log;
     
        procedure flush is
            v_status number;
        begin
            dbms_pipe.pack_message('$$END$$');
            v_status := dbms_pipe.send_message('log');
        end;
     
        function get_log return log_array pipelined is
            v_status  number;
            v_message varchar2(4000);
        begin
            while true loop
                v_status := dbms_pipe.receive_message('log');
            if v_status = 0 then
            dbms_pipe.unpack_message(v_message);
            if v_message = '$$END$$' then
                return;
            end if;
            pipe row(v_message);
            pipe row('');
            end if;
            end loop;
            return;
        end get_log;
    end;
 /

2、測試
-- session #1                                                                                                    
  begin                                                                                                        
      for idx in 1 .. 10 loop                                                                                  
          pkg_log.log(idx || 'th insertion');
          dbms_lock.sleep(1);                                                                                  
      end loop;                                                                                                
      pkg_log.flush;                                                                                           
  end;                                                                                                         
  /               

 -- session #2                                                                                                
  set array 2                                                                                                  
  set pages 100                                                                                                
  select * from table(pkg_log.get_log);     

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-624007/，如需轉載，請註明出處，否則將追究法律責任。