Отладка PL/SQL кода для внешней сессии БД

Проблематика и назначение:

Периодически Oracle разработчики сталкиваются с проблемой отладки PL/SQL кода, когда код вызывается из веба или среднего слоя(т.е. когда сессия разработчика не совпадает с сессией в которой возникает проблема).

Особенно актуально, если какие-либо проблемы возникают на стороне Web при двухзвенных и трехзвенная схемах взаимодействия БД и Web(ниже пример трехзвенной архитектуры взаимодействия):

Рисунок 1 — Трехзвенная архитектура взаимодействия БД и Web.

Метод решения проблем:

В решение проблем с отладкой PL/SQL кода нам помогут пакеты:

• DBMS_PIPE — Пакет который позволяет отпавлять сообщения(пайпы) между 2мя сессиями БД Oracle.
• DBMS_ALERT — Пакет, который обеспечивает поддержку асинхронных оповещений для различных событий БД Oracle.

Ниже код метода, который мы будем отлаживать при помощи DBMS_PIPE и DBMS_ALERT одновременно:

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;

При отсутствие грантов на DBMS_PIPE и DBMS_ALERT раздадим их:

Рисунок 2 — Раздача грантов c Oracle сервера схемы SYS на рабочую схему

Отловим сообщения для DBMS_PIPE и DBMS_ALERT при помощи PL/SQL Developer:

Отлавливание сообщений при помощи кода не рассматриваю, т.к. информации достаточно в Oracle DOC и на просторах интернета.
Заходим в Tools→Event Monitor..., в одном окне выбираем тип события "Pipe", а в другом "Alert" в Event name указываем название пайпы и алерта, которые задали в коде и нажимаем Start:


Рисунок 3 — Настройка окна с Pipe


Рисунок 4 — Настройка окна с Alert

После запуска метода checkout_with_pipe_and_alert из веба/среднего слоя(в нашем случае из другой сессии):

begin
  checkout_with_pipe_and_alert(5);
end;

В окнах Pipe и Alert получим следующие результаты:

Рисунок 5 — Результат получения информации от Pipe


Рисунок 6 — Результат получения информации от Alert

Выводы:

• dbms_pipe отличный метод, для отладки pl/sql в разных сессиях, только pipe периодически забивается и приходится использовать метод: dbms_pipe.purge
• dbms_alert я бы не советовал использовать, т.к. периодически теряются сообщения при отладке(как видно из рисунка 6), быть может не правильно его использую. Если кто-то с таким сталкивался, напишите в комментариях и я поправлю статью.