Long-Term PL / SQL Block Monitoring

Question

I have a pretty intense PL / SQL block that builds fingerprints from molecular structures. I would like to print the output to the SQL * Plus console to provide feedback on how many structures were processed. I can do it with dbms_output.put_line

However, every time you call a new line, it is written. I want to rewrite the string.

For example, I currently have below.

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

In the end, I fill up the buffer as I deal with thousands of structured records.

Is it possible to use a method that will simply overwrite the last output line?

Answer 1

Using DBMS_OUTPUT means that SQL * Plus will not display anything until the entire PL / SQL block is complete, and then will display all the data in the buffer. Therefore, this is not an appropriate way to ensure permanent status.

Oracle, on the other hand, provides the DBMS_APPLICATION_INFO package, which is specifically designed to help you keep track of current code. For example, you can 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; 

In a separate SQL * Plus session, you can track progress by requesting a view of V$SESSION_LONGOPS

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

Answer 2

You can also send messages to the named pipe, and another process is to 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; 

Answer 3

I do not think you can. As far as I understood dbms_output, this just doesn't work.

I recommend that you use put to echo one point and a new line every 1000 or so records to see that something is happening, and write to the table or arrange the current position so you can see if you want to know.