Debugging PL / SQL code for an external DB session

Problems and purpose:

Periodically, Oracle developers are faced with the problem of debugging PL / SQL code when the code is called from the web or the middle layer (that is, when the developer session does not match 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 DB and Web interaction schemes (below is an example of a three-tier interaction architecture):

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

Problem solving method:

Packages will help us in solving problems with debugging PL / SQL code:

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

Below is the method code 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 distribute them:

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

Let's catch messages for DBMS_PIPE and DBMS_ALERT using PL / SQL Developer:

I do not consider trapping messages using a code, since Enough information in Oracle DOC and on the Internet.
Go to Tools → Event Monitor ... , in one window, select the event type " Pipe ", and in the other " Alert " in the Event name specify the name of the pipe and alert that you set in the code and click Start :


Figure 3 - Setting up a window with Pipe


Figure 4 - Customize the window with the Alert

After starting 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 - Result of receiving information from Pipe


Figure 6 - Result of receiving information from the Alert

Findings:

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