Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to display status in long PL/SQL jobs?
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;
SQL>
SQL> SPOOL OFF
Received on Mon Dec 02 1996 - 00:00:00 CST