Re: Q: DBMS_OUTPUT display while processing?
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 ad34 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 CorporationReceived on Tue Aug 10 1999 - 21:56:36 CEST