| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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?
You might want to look at the dbms_application_info package that comes with 7.2 and up as well.
It allows you to set three columns in v$session - MODULE, ACTION, and CLIENT_INFO. You can use MODULE to identify your process. You can use ACTION to notify yourself of what step you are executing. You can use client_info for anything else you want.
that way, you can kick off the long running pl/sql block (submit it asyncronously using dbms_job if you want) and monitor it's progress in the v$session table.
On Tue, 03 Dec 1996 06:41:37 GMT, zsero_at_mail.idt.net (Zev Sero) wrote:
>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
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |