ORACLE日志记录工具

--删除日志表
begin
  execute immediate 'drop table COM_LOGGER';
exception 
  when others then null;
end;
/
--创建日志表
create table COM_LOGGER
(
  log_id    NUMBER not null,
  log_text  VARCHAR2(4000),
  log_key   VARCHAR2(1000),
  log_value VARCHAR2(1000),
  log_level VARCHAR2(20),
  log_date  DATE
);
alter table COM_LOGGER add constraint PK_COM_LOGGER primary key (log_id);
--表注释
comment on table COM_LOGGER
  is '系统日志表，记录相关的日志信息';
--表字段注释
comment on column COM_LOGGER.log_id
  is '日志序号，由序列控制';
comment on column COM_LOGGER.log_text
  is '日志内容';
comment on column COM_LOGGER.log_key
  is '日志来源名称：PROCESS_INST_ID,NODE_INST_ID等';
comment on column COM_LOGGER.log_value
  is '日志来源内容：PROCESS_INST_ID,NODE_INST_ID等的值';
comment on column COM_LOGGER.log_level
  is '日志等级：DEBUG,INFO,WARN,ERROR';
comment on column COM_LOGGER.log_date
  is '日志产生时间';
--创建日志序列
begin
  execute immediate 'create sequence seq_com_logger';
exception 
  when others then
    null;
end;
/

create or replace package logger is

  -- Author  : ATTWICE
  -- Created : 2012/12/06 09:17:00
  -- Purpose : 日志记录

  -- Public constant declarations
  log_level_debug constant varchar2(10) := 'debug';
  log_level_info  constant varchar2(10) := 'info';
  log_level_warn  constant varchar2(10) := 'warn';
  log_level_error constant varchar2(10) := 'error';

  procedure debug(log_text  varchar2,
                  log_key   varchar2 default null,
                  log_value varchar2 default null);

  procedure info(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null);

  procedure warn(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null);

  procedure error(log_text  varchar2,
                  log_key   varchar2 default null,
                  log_value varchar2 default null);

  procedure log(log_text  varchar2,
                log_key   varchar2 default null,
                log_value varchar2 default null,
                log_level varchar2 default 'debug');
end logger;
/
create or replace package body logger is

  procedure debug(log_text  varchar2,
                  log_key   varchar2 default null,
                  log_value varchar2 default null) is
  begin
    log(log_text, log_key, log_value, log_level_debug);
  end;

  procedure info(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null) is
  begin
    log(log_text, log_key, log_value, log_level_info);
  end;

  procedure warn(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null) is
  begin
    log(log_text, log_key, log_value, log_level_warn);
  end;

  procedure error(log_text  varchar2,
                  log_key   varchar2 default null,
                  log_value varchar2 default null) is
  begin
    log(log_text, log_key, log_value, log_level_error);
  end;

  procedure log(log_text  varchar2,
                log_key   varchar2,
                log_value varchar2,
                log_level varchar2 default 'debug') is
    pragma autonomous_transaction;
  begin
    insert into com_logger
      (log_id, log_text, log_key, log_value, log_level, log_date)
    values
      (seq_com_logger.nextval, log_text, log_key, log_value, lower(log_level), sysdate);
    commit;
  exception
    when others then
      rollback;
      log('sqlcode=' || sqlcode || ',sqlerrm=' || sqlerrm,
          'function logger.log',
          log_key || '-' || log_value,
          'error');
  end;

end logger;
/