实时监控存储过程中应用的日志信息

       几年前开发人员问我，要怎样解决存储过程在执行过程中，能实时看到日志生

成，因为无论采用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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-624007/