Error logging/debugging mechanism for PL/SQL code

Question

I want to create an error logging and debugging mechanism for my PLSQL code. There will be a log_Error table in which i will inserting the errors/debugs.

I am planning to insert debugs periodically in my code so that it will be easy for me to identify till which point my code is getting execution

In the exception section i will be inserting the error messages in this log table.

Also,I want a mechanism in which i can enable this logging mechanism for a particular session instead of all the sessions by default. If this logging happens by default, it will create unnecessary performance impact and create unwanted logs

Can you please suggest and approach in which i am able to enable/disable logging mechanism for a session manually?

If you're doing this as a personal project, fair enough. But if you're writing a logging package for work I suggest you don't. Instead you should use the open source logger library. It's the closest we have to an industry standard for logging PL/SQL. Find it here — APC
– APC, Commented Mar 14, 2019 at 14:08

F.Madsen · Accepted Answer · 2019-03-14 08:55:26Z

1

You can create a small logging package where you set a flag per session like this

create package debug_log_pk as
  bLogflag boolean := false;
end debug_log_pk;

then create a procedure that insert data into your table:

create or replace procedure log_error( ..... )
as
   pragma autonomous_transaction;
begin
   if debug_log_pk.bLogflag then
     insert into logging_table (...) values (...);
     commit;
   end if;
end;

Somewhere in your program. set:

debug_log_pk.bLogflag := true;

That can be done anywhere in your application code before you want to log, and will apply for the reset of the session. And you can turn logging off as well :)

Also the pragma autonomous_transaction; puts the logging into a separate transaction so it will survive a rollback in the db.

Also have a look at this: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1171766400346817259

edited Mar 14, 2019 at 8:55

answered Mar 14, 2019 at 8:49

F.Madsen

7124 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error logging/debugging mechanism for PL/SQL code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related