Re: How to display status in long PL/SQL jobs?

From: Zev Sero <zsero_at_mail.idt.net>
Date: 1996/12/03
Message-ID: <32a3c906.5079189@news.idt.net>#1/1

zm = Zernan Martinez wrote:
th = Thomas Harleman <harleman_thomas_l_nonlilly_at_lilly.com> wrote:  

zm> I have a PL/SQL or SQL  block that executes a fair amount of time.
zm> Can anybody show me a simple way of reporting the status of the
zm> query as it processes. Some thing like . . .

th> Look into the DBMS_OUTPUT package that comes with PL/SQL. You'll
th> need to use SQL*Plus to see the results. Inside your procedure you
th> must include a line similar to this:
th> DBMS_OUTPUT.PUT_LINE('END OF PROCEDURE');

th> In order to do what you want, you will need to write a PL/SQL th> loop. Here is the output to a test program.

th> SQL> SET SERVEROUTPUT ON
th> SQL> DECLARE
th>  2    CURSOR C IS
th>  3  	 SELECT DISTINCT A FROM TABLE1;
th>  4    CURSOR B (ACHAR CHAR) IS
th>  5  	 SELECT SUM(B) FROM TABLE1 WHERE A = ACHAR;
th>  6  BEGIN
th>  7    FOR A IN C LOOP
th>  8  	 FOR D IN B(A.A) LOOP
th>  9  	   -- DO SOMETHING WITH THE SUM IN D.
th> 10  	   -- REPORT WHICH VALUE HAS BEEN SUMMED.
th> 11  	   DBMS_OUTPUT.PUT_LINE(A.A||' IS DONE.');
th> 12  	 END LOOP;
th> 13    END LOOP;
th> 14  END;
th> 15  /
th> A IS DONE.

th> B IS DONE.

th> C IS DONE.

th> PL/SQL procedure successfully completed. th> SQL> SPOOL OFF

Bzzzt. This does *not* do what zm wants. The flaw is that serveroutput is not visible until *after* the PL/SQL block returns. By that time we know that everything is done.

What zm wants is some way to tell where the procedure is up to *during* execution. For that, you need to insert rows into a table, and commit that insert, so it'll be visible to another process which can select from that table to see how far the procedure has come. If you can't commit in the middle of your procedure, then I'm afraid I can't think of any way to tell which iteration of a loop you're on.

But if it's not a loop but a series of discrete SQL statements, then you can look at (I think it's called) V$QUERY (or possibly V$SESSION). There's a column which is defined as VARCHAR2(60) which is the first 60 chars of the SQL statement that each session is currently executing, so you can monitor that to see where your procedure is up to.

--
Zev Sero		Don't blame me, I voted for Harry Browne
zsero_at_mail.idt.net
zsero_at_technimetrics.com

Received on Tue Dec 03 1996 - 00:00:00 CST