Re: Q: DBMS_OUTPUT display while processing?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 10 Aug 1999 19:56:36 GMT
Message-ID: <37c2816c.114499111_at_newshost.us.oracle.com>


A copy of this was sent to tedchyn_at_yahoo.com (if that email address didn't require changing) On Tue, 10 Aug 1999 15:04:50 GMT, you wrote:

>In article <37afcc11.6058071_at_newshost.us.oracle.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to ewong74_at_netscape.net
>> (if that email address didn't require changing)
>> On Sat, 07 Aug 1999 23:23:56 GMT, you wrote:
>>
>> >Hi,
>> >
>> >I have a large PL/SQL package with 20 procedures within the package.
>> >The purpose of the package is do some batch process which will take
>> >about 10 hours.
>> >
>> >I tried to have DBMS_OUTPUT.PUT_LINE in every single procedure to
>print
>> >out the status so that I can monitor each process(to see if there is
>> >errors) while the package is still running. However, the DBMS_OUTPUT
>> >didn't display until the whole package is done. This is very
>annoying.
>> >Is there any work around?
>> >
>> >Thanks in advance.
>> >
>> >Ed
>> >
>> >
>> >Sent via Deja.com http://www.deja.com/
>> >Share what you know. Learn what you don't.
>>
>> dbms_output cannot be display until the procedure is done executing.
>>
>> one option that works very well is the dbms_application_info. this
>allows you
>> to set values in the v$session table (to tell where you are) and in
>8.0 and up,
>> to put many rows in the V$SESSION_LONGOP table with the
>> dbms_application_info.set_session_longops procedure. these rows are
>visible
>> immediately (no commit needed) so they are perfect for monitoring
>long running
>> procedures from any terminal (a simple query shows the status of a
>long running
>> procedure that has calls to dbms_application_info in it)....
>>
>> --
>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
>Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated
>June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>Thomas, Can you give us an example for how to use
>dbms_application_info.set_session_longops procedure ?

2 examples. One for 8.0, one for 8i, release 8.1.

They changed the interface between versions slightly (for the better).

In v8.0, say you have a long running procedure:

SQL> l
  1 create or replace procedure long_running_procedure   2 as

  3     l_rindex pls_integer default
  4                          dbms_application_info.set_session_longops_nohint;
  5
  6 begin
  7     dbms_application_info.set_client_info( 'This is me' );
  8     dbms_application_info.set_module( 'long_running_procedure', 'starting'
);
  9     for i in 1 .. 100 loop
 10             l_rindex := dbms_application_info.set_session_longops_nohint;
 11   
 12             dbms_application_info.set_session_longops
 13             ( hint        => l_rindex,
 14               context     => 12345,
 15               stepid      => 1000000+i,
 16               stepsofar   => i,
 17               steptotal   => 100,
 18               sofar       => i,
 19               totalwork   => 100,
 20               application_data_1 => 1,
 21               application_data_2 => 2,
 22               application_data_3 => 3 );
 23             dbms_lock.sleep(1);
 24     end loop;

 25* end;

so that procedure registers itself in the v$session table as "This is me". It sets the three fields you can in there (client_info, action, and module). Then it goes into a loop that takes 100 seconds to execute (dbms_lock.sleep for a second every time through). It calls the v8.0 variant of longops.

every time through, we set the 'hint' to dbms_application_info.set_session_longops_nohint. that tells longops to try and use a new row (v$ tables are memory tables -- it will not keep all of your rows in there, only the latest and greatest). We call dbms_application info and can pass it lots of numbers (in v8, can add some strings in v8i/8.1) This data tracks your progress and is user defined (you can derive some meaning from this data).

I can query that up with:

SQL> select context , stepid, stepsofar, steptotal, sofar,

  2             totalwork, application_data_1 ad1,
  3          application_data_2 ad2, application_data_3 ad3
  4 from v$session_longops a, v$session b   5 where a.sid = b.sid
  6 and bitand(a.serial#,b.serial#) = b.serial#   7 and b.client_info = 'This is me'
  8 /

(the wierd bitand join is because serial# is signed in one table and unsigned in the other, big serial#'s go negative -- the bitand gets around that).

that shows (for example)

CONTEXT STEPID STEPSOFAR STEPTOTAL SOFAR TOTALWORK AD1 AD2 AD3 ------- -------- --------- ---------- ----- --------- --- --- ---

  12345  1000006         6        100     6       100   1   2   3
  12345  1000007         7        100     7       100   1   2   3
  12345  1000004         4        100     4       100   1   2   3
  12345  1000005         5        100     5       100   1   2   3


that shows my progress at a point in time -- in this case i'm getting the last 4 rows as shown by stepsofar.

In Oracle8i, release 8.1, the interface has been changed so that I might code:

create or replace procedure long_running_procedure as

    l_rindex pls_integer default

                         dbms_application_info.set_session_longops_nohint;
    l_slno pls_integer default NULL;

begin

    dbms_application_info.set_client_info( 'This is me' );     dbms_application_info.set_module( 'long_running_procedure', 'starting' );

    for i in 1 .. 100 loop

        l_rindex := dbms_application_info.set_session_longops_nohint;
        l_slno   := NULL;
        dbms_application_info.set_session_longops
        ( rindex      => l_rindex,
          slno        => l_slno,
          op_name     => 'LONG_RUNNNIG_PROCEDURE ' || i,
          target      => 1000000+i,
          context     => 2000000+i,
          sofar       => i,
          totalwork   => 100,
          target_desc => 'What I''m Working On',
          units       => 'numbers 1-100' );

        dbms_lock.sleep(1);

    end loop;
end;
/

here, I can put in more descriptive strings and such, in addition to some numbers to show progress. the query would be a little different (different columns in the v$ table) but the concept is the same.

>Thanks Ted Chyn(tedchyn_at_yahoo.com)
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Aug 10 1999 - 21:56:36 CEST

Original text of this message