witch Pl/Sql logging on/off for specific sessions

 As we know Oracle provides several nice tools to debug our PlSql code and even a profiler to get all the details about code execution. But what if you want to use your own debug code and be able to switch it on for specific sessions whenever you want? And I mean, obviously, without changing the code! I’ve seen around some examples in which people use a configuration table to do that: the debug code keeps querying the table and logs the output only if the flag is Y. This way of doing it is absolutely fine and works well but I was looking for something more elegant that does not involve a table. My idea takes advantage of the Oracle application contexts and it’s really simple.

First of all, we have to create a global context. The “create any context” privilege is not commonly granted to normal users by default so we might need to do it from SYS or ask somebody to do it for us.
Let’s call this context CTX_DEBUG and make it refer to the SCOTT.PCK_DEBUG_UTILS package that we are going to create right after

1	create or replace context ctx_debug using scott.pck_debug_utils accessed globally;

Now let’s login with SCOTT and create the package PCK_DEBUG_UTILS that will support the ctx_debug context

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create or replace package pck_debug_utils as   procedure set_parameter(p_session in number,                           p_enabled in number); end pck_debug_utils; / create package body pck_debug_utils as   procedure set_parameter(p_session in number,                           p_enabled in number)   is   begin     dbms_session.set_context(namespace => 'ctx_debug',                              attribute => p_session,                              value     =>  p_enabled);   end;   end pck_debug_utils; /

and, just to test our code, create a very simple log table

1	create table mylog(txt varchar2(100), ts timestamp);

Almost everything is ready apart from the logging procedure that populates our log table. It has to be written in a way that allows us to turn on/off the logging activity so let’s add the LOG procedure to our package

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

create or replace package pck_debug_utils as   procedure set_parameter(p_session in number,                           p_enabled in number);     procedure log(p_in_text in varchar2); end pck_debug_utils; / create or replace package body pck_debug_utils as   procedure set_parameter(p_session in number,                           p_enabled in number)   is   begin     dbms_session.set_context(namespace => 'ctx_debug',                              attribute => p_session,                              value     =>  p_enabled);   end;     procedure log(p_in_text in varchar2) is   pragma autonomous_transaction;   begin     if NVL(sys_context('ctx_debug', sys_context('userenv', 'sid')), 0) = 1 then -- debug is active for this session       insert into mylog(txt, ts)       values(p_in_text, systimestamp);       commit;     end if;   end log;   end pck_debug_utils; /

Oracle application contexts create name-value pairs that can be set and requested. By calling  sys_context on our newly created ctx_debug context and passing the current session id, we are requesting the value for a global parameter called like our session id. If the return value (using NVL because it can be null if the parameter is missing) is 1 then the log is written to the table.
Now let’s run a simple test to see how this works. Let’s run the following small PlSql block in 2 different sessions at the same time. As you can see it calls the PCK_DEBUG_UTILS.LOG procedure

1 2 3 4 5 6 7

begin   for i in 1..1000 loop     pck_debug_utils.log('Debug message for session '||sys_context('userenv', 'sid'));     dbms_lock.sleep(3);   end loop; end; /

If we check the log table MYLOG, there are no rows being created. Out DEBUG is still OFF! Let’s query v$sesion to get the SID of our running scripts, let’s say 155 and 158. If we want to enable logging for session 155, all we have to do is

1 2 3 4

begin   pck_debug_utils.set_parameter(p_session => 155,p_enabled => 1); end; /

If we query out MYLOG table now we’ll see logs coming from that specific session 155, cool isn’t it? We can enable the other one and disable logging for 155

1 2 3 4 5

begin   pck_debug_utils.set_parameter(p_session => 155,p_enabled => 0);   pck_debug_utils.set_parameter(p_session => 158,p_enabled => 1); end; /

and finally, also disable logging for session 158

1 2 3 4

begin   pck_debug_utils.set_parameter(p_session => 158,p_enabled => 0); end; /

I find this a nice way to implement a logging code that can be turned on in case of need on specific sessions and without changing any code.
As always, hope this is useful to some of you!