Ny prevalent logging packages in PL/SQL similar to java and .Net counterparts

best practicesoracleplsql

Normally in java or C# , I would use a following construct when try and catch errors.

Bad Exception Handling in C# and Java

try
{
  // do something
}
catch(Exception ex)
{
}

Good Exception Handling in Java and C#

try
{

}
catch(ArgumentException ex)
{
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace 
 throw;

}
catch(Exception ex)
{
  // this is catch ALL others block
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace 
 throw;
}

In C# and java , I would use logging libraries.
java : log4j,
see others
https://stackoverflow.com/questions/3042450/what-log4j-alternative-logging-libraries-are-available

.Net : log4net, Nlog etc

In PL/SQL

Bad Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  
   null;
   WHEN OTHERS THEN  
   null;
END;

Good Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  
   LOG
   RAISE 

   WHEN OTHERS THEN  
   LOG
   RAISE 
END;

a simple google search brings log4plsql package : http://log4plsql.sourceforge.net/

  • Are there any other libraries?
  • Are they prevalent in PL/SQL programming?

Oracle Documentation for Error Handling does not give hints about logging.

It gives following sentence and uses dbms_output.put_line for logging:

"Sometimes, you want to reraise an exception, that is, handle it
locally, then pass it to an enclosing block. For example, you might
want to roll back a transaction in the current block, then log the
error in an enclosing block."

Best Answer

Prevalent? No. It's not uncommon, however, to write a procedure like so:

create or replace procedure write_to_log
  (p_entry_point in varchar,
   p_log_type    in varchar,
   p_message     in varchar)
as
pragma autonomous_transaction
begin
  insert into my_log_file
    (entry_point, log_type, message, log_timestamp)
  values
    (p_entry_point, p_log_type, p_message, systimestamp);
  commit;
end;

Then you can add calls to write_to_log where needed throughout the code.

Related Question