Как в реальном времени получить фидбэк от хранимой процедуры?

#oracle #plsql #процедуры

На основе представления обновляю и вставляю миллионы строк в других таблицах посредством
хранимой процедуры. Выполняется очень долго до нескольких часов.
Поэтому хотелось бы получать какой-то фидбэк от хранимой процедуры. C dbms_output.put_line
проблема в том, что вывод приходит только тогда, когда хранимая процедура завершает
выполнение. 

Есть ещё какие-нибудь варианты кроме dbms_output.put_line?
    

Ответы

Ответ 1

Пусть процедура пишет лог своих действий (или только ошибок). Из таблицы-лога дергайте
записи.
У нас почти все процедуры логи пишут, потом достаточно удобно отслеживать правильность
действий.


Ответ 2

Чтобы получить фидбэк от процесса выполнения хранимой процедуры в реальном времени,
необходимо обеспечить обмен данными между двумя сессиями. Одной, в которой хранимая
процедура выполняется, и другой, в которой надо получить фидбэк.
Основным механизмом такого обмена является db pipes, который напомиминает named pipes
в Unix и многие инструменты в БД Oracle используют его, например dbms_alert.
Низкоуровневый пакет для него dbms_pipe. Пример для реализации следует.

В первой сессии запустите блок, который ожидает фидбэк от "долгоиграющей" процедуры.
Он как-бы подвиснет, ожидая этот фидбэк:

begin 
    declare msg notification; 
    begin <> loop  
        msg := notification.receive;
        dbms_output.put_line (msg.print);
        exit receiveUntilEOF when msg.text = 'EOF';
    end loop receiveUntilEOF; end;
end;
/


Во второй сессии запустите "долгоиграющую" процедуру:

begin
    notification.send ('start');
    for i in 1..3 loop
        dbms_lock.sleep (3);
        notification.send ('part '||i||' finished');
    end loop;    
    notification.send ('EOF');
end;
/


В первой сессии выведет сообщения отправленные во второй сессии. Время отправки и
приёма совпадают, т.е. асинхронный фидбэк в реальном времени:

received at 16:55:18 sent at 16:55:18: start
received at 16:55:21 sent at 16:55:21: part 1 finished
received at 16:55:24 sent at 16:55:24: part 2 finished
received at 16:55:27 sent at 16:55:27: part 3 finished
received at 16:55:27 sent at 16:55:27: EOF


Пользовательский тип, который необходимо предварительно создать для демонстрации
примера: 

create or replace type notification as object (
    dt date,
    text varchar2 (32676),
    static procedure send (text varchar2),
    static function receive return notification,
    member function print return varchar2
);
/
create or replace type body notification as
    static procedure send (text varchar2) is
        result  integer;
    begin
        dbms_pipe.pack_message(sysdate);
        dbms_pipe.pack_message(text);
        result := dbms_pipe.send_message('pipe$notification');
        if result != 0 then
            raise_application_error (-20001, 'notification pipe send error result='||result);
        end if;
    end;
    static function receive return notification is
        result integer;
        ret notification := notification (null, null);
    begin
        result := dbms_pipe.receive_message (pipename => 'pipe$notification');
        if result = 0 then
            dbms_pipe.unpack_message (ret.dt);
            dbms_pipe.unpack_message (ret.text);
        else
            raise_application_error (-20002, 'notification.receive errror result='||result);
        end if;
        return ret;
    end;
    member function print return varchar2 is
    begin 
        return 'received at '||to_char (sysdate, 'hh24:mi:ss')||' sent at '||to_char
(dt, 'hh24:mi:ss')||': '||text;
    end;
end;
/


Ответ 3

Простой способ получить фидбэк из долго выполняющейся хранимой процедуры с помощью
пакета dbms_application_info. Преимущество: простота реализации, а недостатки: не асинхронный
опрос, только 64 байта информации, нет истории.

Надо добавить в процедуру информацию о выполнении:  

dbms_application_info.set_module (
    module_name=>'myLongPlayOp', action_name=>'insert_multiple_Tables');

dbms_application_info.set_client_info(client_info=>'insert into table1 start');
-- insert into table1 ...    
dbms_application_info.set_client_info(client_info=>'insert into table1 done..table2
start');
-- insert into table2 ...    
dbms_application_info.set_client_info(client_info=>'insert into table2 done');


Опрашивать можно так, где sid=135 идентификатор сессии, где выполняется хранимая
процедура:

select sid, module||'.'||action||': '||client_info from v$session where sid=135;


В различный момент времени запрос вернёт последнее состояние процесса выполнения:

135 myLongPlayOp.insert_multiple_Tables: insert into table1 start
135 myLongPlayOp.insert_multiple_Tables: insert into table1 done..table2 start
135 myLongPlayOp.insert_multiple_Tables: insert into table2 done


Ответ 4

dbms_pipe  - быстро и с минимальными затратами.


Ответ 5

Вот так можно с dbms_alert.

Запустить приемник:

 DECLARE
     message VARCHAR2(1800) := 'NONE';
     status NUMBER := -1;
 BEGIN
     dbms_alert.register('status$_queue');
     <> LOOP
         dbms_alert.waitone('status$_queue', message, status);
         dbms_output.put_line(sysdate||': received status '||status||' message '||message);
         IF status != 0 OR message = 'END_OF_TX' THEN
             EXIT reading;
         END IF;
     END LOOP reading;
     dbms_alert.remove('status$_queue');
 END;
 /


В другом окне запустить эмулятор процедуры:

 DECLARE
     PROCEDURE signal (message VARCHAR2) is
     PRAGMA autonomous_transaction;
     BEGIN
         dbms_alert.signal ('status$_queue', message);
         COMMIT;
     END;
 BEGIN
     signal('START');
     dbms_lock.sleep(1);
     signal('got chunk #1 done');
     dbms_lock.sleep(1);
     signal('got chunk #2 done');
     signal('END_OF_TX');
 END;
 /


Вывод в приемнике:

2018-03-26  19:56:28: received status 0 message START
2018-03-26  19:56:29: received status 0 message got chunk #1 done
2018-03-26  19:56:30: received status 0 message got chunk #2 done
2018-03-26  19:56:30: received status 0 message END_OF_TX