Based on the view, I update and insert millions of rows in other tables through a stored procedure. It takes a very long time to several hours.
Therefore, I would like to receive some feedback from the stored procedure. C dbms_output.put_line problem is that the output comes only when the stored procedure completes execution.

Are there any other options besides dbms_output.put_line ?

    5 answers 5

    Let the procedure write a log of its actions (or only errors). From the log table, pull the record. We almost all logs write procedures, then it is quite convenient to track the correctness of actions.

      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; / 
      • @Alexandr_TT Burning with shame for such typos, thank you. - 0xdb
      • I am burning down :-) do not pay attention and do not take it to heart. Your answers are so good that, apart from typos, you will not find what to rule. And about typos, - because we are humans, not robots, and we all have a tendency to make mistakes. Do you think I have less ochepyak, far from it ... - Alexandr_TT

      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.

        • With minimal cost of what? Resources, time? - user282693

        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