CODEWITHSUNDEEP
oracle-databaseplsql
Indiecoder
Indiecoder

Reputation: 186

Error logging/debugging mechanism for PL/SQL code

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?

Upvotes: 0

Views: 1357

Answers (1)

F.Madsen
F.Madsen

Reputation: 702

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

Upvotes: 1

Related Questions