Level based Logging in Oracle

Question

Hi,

I am Kanagaraj. In our stored procedure, we are logging messages at 500 places and logs are stored in a table where we are having some performance issues. We need to split these messages into Debug, Info and Error messages. Based on the level, only limited messages should be logged. If necessary, we will be enabling the next level and see the more logs. What could be the effective way for introducing this level based logging in our procedure?.

Thanks in advance.

Kanagaraj.

Answer 1

Something like this...

create or replace package logger as
  min_level number := 2;
  procedure ins_log(level number, message varchar2);
end;

create or replace package body logger as
  procedure ins_log(level number, message varchar2) is
    pragma autonomous_transaction;
  begin
    if level>=min_level then
      insert into loggin(ts, msg) values (sysdate, message);
    end if;
    commit; // autonomous_transaction requires that
  end;
end;

EDIT: Added pragma autonomous_transaction;, thanks Adam

Answer 2

There is a port of log4j for Oracle PL/SQL that can be found on sourceforge. This allows logging to be enabled/disabled at various levels, and for specific packages/functions/procedures simply be modifying a configuration. It also supports redirection to different destinations.

Answer 3

Hi There

Check out PLJ-Logger at https://sourceforge.net/p/plj-logger/home/. Its really easy to implement and has your desired functionality and a lot more. Proper logging built into PL/SQL code will transform it.

P