Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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."

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
add comment

You might be looking for DBMS_UTILITY package:

FORMAT_CALL_STACK Function
FORMAT_ERROR_BACKTRACE Function
FORMAT_ERROR_STACK Function$

However, you have to implement any logging functionality by yourself.

DBMS_UTILITY Documentation

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.