Monitoring long-running PL/SQL block

Question

I have a fairly time intensive PL/SQL block that builds fingerprints from molecular structures. I would like to print output to SQL*Plus console to provide feedback on how many structures have been processed. I can do this with dbms_output.put_line

However everytime that is called a new line is written. I want to overwrite the line.

For example, currently I have the below.

Structure x of y processed Structure x of y processed Structure x of y processed Structure x of y processed

Eventually I fill up the buffer as I'm dealing with thousands of structure records.

Is there a method I can use that will just overwrite the last output line?

Justin Cave · Accepted Answer · 2011-09-08 15:50:11Z

Using DBMS_OUTPUT means that SQL*Plus will display nothing until the entire PL/SQL block is complete and will then display all the data currently in the buffer. It is not, therefore, an appropriate way to provide an ongoing status.

On the other hand, Oracle does provide a package DBMS_APPLICATION_INFO that is specifically designed to help you monitor your running code. For example, you could do something like

CREATE PROCEDURE process_structures AS <<other variable declarations>> rindex BINARY_INTEGER; slno BINARY_INTEGER; totalwork NUMBER := y; -- Total number of structures worksofar NUMBER := 0; -- Number of structures processed BEGIN rindex := dbms_application_info.set_session_longops_nohint; FOR i IN (<<select structures to process>>) LOOP worksofar := worksofar + 1; dbms_application_info.set_session_longops( rindex => rindex, slno => slno, op_name => 'Processing of Molecular Structures', sofar => worksofar , totalwork => totalwork, target_desc => 'Some description', units => 'structures'); <<process your structure with your existing code>> END LOOP; END;

From a separate SQL*Plus session, you can then monitory progress by querying the V$SESSION_LONGOPS view

SELECT opname, target_desc, sofar, totalwork, units, elapsed_seconds, time_remaining FROM v$session_longops WHERE opname = 'Processing of Molecular Structures';

Rene · Accepted Answer · 2011-09-12 10:44:54Z

You may also send messages to a named pipe and have another process read the message from the pipe.

 procedure sendmessage(p_pipename varchar2 ,p_message varchar2) is s number(15); begin begin sys.dbms_pipe.pack_message(p_message); exception when others then sys.dbms_pipe.reset_buffer; end; s := sys.dbms_pipe.send_message(p_pipename, 0); if s = 1 then sys.dbms_pipe.purge(p_pipename); end if; end; function receivemessage(p_pipename varchar2 ,p_timeout integer) return varchar2 is n number(15); chr varchar2(200); begin n := sys.dbms_pipe.receive_message(p_pipename, p_timeout); if n = 1 then return null; end if; sys.dbms_pipe.unpack_message(chr); return(chr); end;

Angelo Fuchs · Accepted Answer · 2011-09-08 15:51:41Z

I don't think you can. As far as I understood the dbms_output it just doesn't work that way.

I recommend you use put to echo a single dot and a newline every 1000 or so entries to see that something is happening and write into a table or sequence the current position so you can have a look if you want to know.

Collectives™ on Stack Overflow

Monitoring long-running PL/SQL block

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related