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