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

Thomas Harleman wrote:
>
> 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
Thomas,

DBMS_OUTPUT may not be suitable here, 'cos it will display the debug info only after the pl/sql procedure/function has finished.

Three solutions :
a. If using 7.3.x, check for a package dbms_application_info.set_module and set_action procedures. The view v$session has two new columns 'module' and 'action' which will reflect what is being set in the procedure.

Eg :

declare
  ..
begin

    dbms_application_info.set_module( 'procedure name','starting procedure' );
...
  for .. loop

        dbms_application_info.set_action( 'in loop' ); ...
...
end;

select sid, module, action
from v$session
where sid = sid of the session executing the procedure

b. Use dbms_pipe to send debug messages on the pipe. Another oracle session will have to keep listening to the pipe and display the debug message. This can work on all versions of 7.x

c. This again needs 7.3.x. USe UTL_FILE to write to a temp file

Hope this helps

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.