Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to display status in long PL/SQL jobs?

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

From: Thomas Harleman <harleman_thomas_l_nonlilly_at_lilly.com>
Date: 1996/12/02
Message-ID: <32A2E651.3268@lilly.com>#1/1

Zernan Martinez wrote:
>
> I have a PL/SQL or SQL block that executes a fair amount of time.
> Can anybody show me a simple way of reporting the status of the query
> as it processes. Some thing like . . .
Zernan:
Look into the DBMS_OUTPUT package that comes with PL/SQL. You'll need to use SQL*Plus to see the results. Inside your procedure you must include a line similar to this:

DBMS_OUTPUT.PUT_LINE('END OF PROCEDURE'); But what you are suggesting is breaking into the execution of the database as it is working. In order to do what you want, you will need to write a PL/SQL loop. Here is the output to a test program. I hope this helps.

Tom Harleman

SQL> CREATE TABLE TABLE1 ( A CHAR(1), B NUMBER)   2 /

Table created.

SQL> BEGIN
  2 FOR A IN 0..4 LOOP
  3 INSERT INTO TABLE1 VALUES (CHR(ASCII('A')+A), A);   4 END LOOP;
  5 END;
  6 /

PL/SQL procedure successfully completed.

SQL> COMMIT; Commit complete.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2 CURSOR C IS
  3 SELECT DISTINCT A FROM TABLE1;
  4 CURSOR B (ACHAR CHAR) IS
  5 SELECT SUM(B) FROM TABLE1 WHERE A = ACHAR;   6 BEGIN
  7 FOR A IN C LOOP

  8  	 FOR D IN B(A.A) LOOP
  9  	   -- DO SOMETHING WITH THE SUM IN D.
 10  	   -- REPORT WHICH VALUE HAS BEEN SUMMED.
 11  	   DBMS_OUTPUT.PUT_LINE(A.A||' IS DONE.');
 12  	 END LOOP;

 13 END LOOP;
 14 END;
 15 /
A IS
DONE. B IS
DONE. C IS
DONE. D IS
DONE. E IS
DONE. PL/SQL procedure successfully completed.

SQL>
SQL> SPOOL OFF Received on Mon Dec 02 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US