Debugging PL / SQL code for an external database session

Issues and purpose:

From time to time, Oracle developers are faced with the problem of debugging PL / SQL code when the code is called from the web or the middle layer (i.e. when the developer session does not coincide with the session in which the problem occurs).

It is especially important if any problems arise on the Web side with two-tier and three-tier schemes for interacting with the database and the Web (below is an example of a three-tier architecture of interaction):

Figure 1 - Three-tier architecture for the interaction of the database and the Web.

Problem Solving Method:

The following packages will help us solve problems with debugging PL / SQL code:

• DBMS_PIPE - A package that allows you to send messages (pipes) between 2 sessions of an Oracle database.
• DBMS_ALERT - A package that provides support for asynchronous alerts for various Oracle database events.

Below is the code of the method that we will debug using DBMS_PIPE and DBMS_ALERT at the same time:

create or replace procedure checkout_with_pipe_and_alert(p_cycle_size in number) is
    c_method_error         constant number := -20000;
    c_method_error_message constant varchar2(4000) := 'Cycle size should be > 0';
    l_power_value number;
    l_i_value     number := 1;
    l_pipe        pls_integer;
begin
    if p_cycle_size > 0 then
        for i in 1 .. p_cycle_size
        loop
            l_power_value := power(i, 2);
            l_i_value := l_i_value * i;
            --Send pipe info
            l_pipe := dbms_pipe.create_pipe(pipename => 'pipe');
            dbms_pipe.pack_message(i || '.l_power_value:=' || l_power_value || ' l_i_value=' || l_i_value);
            l_pipe := dbms_pipe.send_message(pipename => 'pipe');
            --Send alert info
          dbms_alert.signal(name => 'alert', message => i || '.l_power_value:=' || l_power_value || ' l_i_value=' || l_i_value);
        end loop;
    else
        raise_application_error(c_method_error, c_method_error_message);
    end if;
end checkout_with_pipe_and_alert;

If there are no grants for DBMS_PIPE and DBMS_ALERT, we will distribute them:

Figure 2 - Distribution of grants from Oracle SYS schema server to the working scheme

Catch messages for DBMS_PIPE and DBMS_ALERT using PL / SQL Developer:

I do not consider catching messages using code, because enough information in Oracle DOC and on the Internet.
Go to Tools → Event Monitor ... , in one window select the type of event " Pipe ", and in the other " Alert " in the Event name indicate the name of the pipe and alert that were set in the code and click Start :


Figure 3 - Setting the window with Pipe


Figure 4 - Configuring a window with Alert

After running the checkout_with_pipe_and_alert method from the web / middle layer (in our case, from another session):

begin
  checkout_with_pipe_and_alert(5);
end;

In the Pipe and Alert windows we get the following results:

Figure 5 - The result of receiving information from Pipe


Figure 6 - The result of receiving information from Alert

Conclusions:

• dbms_pipe is an excellent method, for debugging pl / sql in different sessions, only pipe is periodically clogged and you have to use the method: dbms_pipe.purge
• I would not recommend using dbms_alert, because Messages are periodically lost during debugging (as can be seen from Figure 6), maybe I’m not using it correctly. If someone came across this, write in the comments and I will correct the article.