How to get feedback from a stored procedure in real time?

To get feedback from the process of executing a stored procedure in real time, it is necessary to provide data exchange between two sessions. One in which the stored procedure is executed, and the other in which you need to get feedback.
The main mechanism of this exchange is db pipes , which resembles named pipes in Unix and many tools in the Oracle database use it, for example, dbms_alert . Low-level package for him dbms_pipe . An example for implementation follows.

In the first session, start the block that expects feedback from the “long-playing” procedure. He seems to hang, waiting for this feedback:

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

In the second session, run the "long-playing" procedure:

 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; / 

In the first session will display messages sent in the second session. The sending and receiving times are the same, i.e. Real-Time Asynchronous Feedback:

 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 

Custom type that you must first create to demonstrate an example:

 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; / 

An easy way to get feedback from a long-running stored procedure using the dbms_application_info package. Advantage: ease of implementation, and disadvantages: not asynchronous polling, only 64 bytes of information, no history.

It is necessary to add execution information to the procedure:

 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'); 

You can interrogate as follows, where sid=135 is the session identifier, where the stored procedure is executed:

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

At a different point in time, the request will return the last state of the execution process:

 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 

dbms_pipe - quickly and with minimal cost.

This is how it is possible with dbms_alert .

Start the receiver:

  DECLARE message VARCHAR2(1800) := 'NONE'; status NUMBER := -1; BEGIN dbms_alert.register('status$_queue'); <<reading>> 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; / 

In another window run the emulator procedure:

  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; / 

Output in receiver:

 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