推荐订阅源

包头:
create or replace package ErrorLog is

  -- Author  : tuil
  -- Created : 2006-9-18 13:45:53
  -- Purpose :

  -- Public type declarations
  TYPE type_cur IS REF CURSOR;
  procedure InsertLogError(ErrorId     in varchar2,
                                       Application in varchar2,
                                       Host        in varchar2,
                                       Type        in varchar2,
                                       Source      in varchar2,
                                       Message     in varchar2,
                                       User        in varchar2,
                                       AllXml      in clob default null,
                                       StatusCode  number,
                                       TimeUtc     in date);
  procedure GetMasterErrorsXml(Application in varchar2,
                         PageIndex   in number,
                         PageSize    in number,
                         TotalCount  out number,
                         v_cur       out type_cur);
  procedure GetDetailErrorXml(Application in varchar2,
                              ErrorId     in varchar2,
                              v_cur       out type_cur);

end ErrorLog;
包体:
create or replace package body ErrorLog is

  procedure InsertLogError(ErrorId     in varchar2,
                       Application in varchar2,
                       Host        in varchar2,
                       Type        in varchar2,
                       Source      in varchar2,
                       Message     in varchar2,
                       User        in varchar2,
                       AllXml      in clob default null,
                       StatusCode  number,
                       TimeUtc     in date) is
  begin
    insert into s_errorlog
      (errorid,
       application,
       host,
       type,
       source,
       message,
       username,
       statuscode,
       timeutc,
       SEQUENCE,
       allxml)
    values
      (ErrorId,
       Application,
       Host,
       Type,
       Source,
       message,
       User,
       StatusCode,
       timeutc,
       SEQ_S_ERRORLOG.Nextval,
       AllXml);
  end InsertLogError;
  procedure GetMasterErrorsXml(Application in varchar2,
                               PageIndex   in number,
                               PageSize    in number,
                               TotalCount  out number,
                               v_cur       out type_cur) is
 
    v_sql   VARCHAR2(1000);
    v_count number;
    v_Plow  number;
    v_Phei  number;
  Begin
    -----------------------------------------取分页总数
    v_sql := 'select count(*) from s_errorlog t where t.Application=''' ||
             Application || '''';
    execute immediate v_sql
      into v_count;
    TotalCount := ceil(v_count / PageSize);
    --------------------------------------------显示任意页内容
    v_Plow := PageIndex * PageSize + 1;
    v_Phei := v_Plow + PageSize - 1;
    v_sql  := 'SELECT ErrorId,
    Application,
    Host,
    Type,
    Source,
    Message,
    username,
    StatusCode,
    TimeUtc
    FROM (SELECT ErrorId,
        Application,
        Host,
        Type,
        Source,
        Message,
        username,
        StatusCode,
        TimeUtc, row_number() over(ORDER BY Sequence DESC) rn
    FROM  s_errorlog a WHERE A.application = ''' || Application || ''')
    WHERE rn BETWEEN ' || v_Plow || ' AND ' || v_Phei || '';
    open v_cur for v_sql;
 
  end;

  procedure GetDetailErrorXml(Application in varchar2,
                              ErrorId     in varchar2,
                              v_cur       out type_cur) is
    v_sql VARCHAR2(1000);
  begin
    v_sql := 'select allxml from s_errorlog t where t.application = ''' ||
             Application || ''' and t.errorid =''' || ErrorId || '''';
    open v_cur for v_sql;
  end;
end ErrorLog;
表结构:
create table S_ERRORLOG
(
  ERRORID     VARCHAR2(50) not null,
  APPLICATION VARCHAR2(60),
  HOST        VARCHAR2(50),
  TYPE        VARCHAR2(100),
  SOURCE      VARCHAR2(60),
  MESSAGE     VARCHAR2(500),
  USERNAME    VARCHAR2(50),
  STATUSCODE  NUMBER,
  TIMEUTC     DATE,
  SEQUENCE    NUMBER,
  ALLXML      CLOB
)