Xref: alice comp.databases.oracle.server:24162
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.server
Subject: Re: check how many rows processed of a sql statement
Date: Tue, 07 Jul 1998 17:01:50 GMT
Organization: Oracle Government
Lines: 129
Message-ID: <35a3544b.15192475@192.86.155.100>
References: <6np80f$43r$0@204.179.92.57> <35a1da39.4429529@192.86.155.100> <6nrnj5$s6j$0@204.179.92.135>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to dshi@magpage.com (David Shi)
(if that email address didn't require changing)
On 6 Jul 1998 23:45:41 GMT, you wrote:

>Thanks Tom, I was actually wondering whether it is possible to check before a
>long-running sql statement get finished, that how many rows has been processed,
>which gives me an idea about how far is the job. Is this possible?
>


if you do an insert/update/delete/select, it will not return until it is
complete.  So, for a single insert/update/delete/select statement, not really
(but then again, yes from another session for insert/update/delete statements,
show you that in a minute).

On the other hand, if you are procedurally processing the statment in a pl/sql
block, yes, you can let another session see how far it has gotten....

So, lets say you wanted to be able to track the process of a long running
update/insert/delete statement.  You need 2 sessions for this.  One to run the
DML and the other can watch it.

Consider a table like:

create table x ( x int );

create or replace package x_pkg 
as
     g_cnt    number;
end;
/

create or replace trigger x_biud
before insert or update or delete on X
begin
   x_pkg.g_cnt := 0;
   if ( inserting ) then
      dbms_application_info.set_module( 'X', 'Inserting' );
   elsif (updating ) then
      dbms_application_info.set_module( 'X', 'Updating' );
   else
      dbms_application_info.set_module( 'X', 'Deleting' );
   end if;
end;
/

create or replace trigger x_aiud_fer
after insert or update or delete on X
for each row 
begin
    x_pkg.g_cnt := x_pkg.g_cnt + 1;
    if ( mod( x_pkg.g_cnt, 100 ) = 0 ) then
        dbms_application_info.set_client_info( to_char( x_pkg.g_cnt ) );
    end if;
end;
/

create or replace trigger x_aiud
after insert or update or delete on X
begin
   dbms_application_info.set_module( 'X', 'Done' );
   dbms_application_info.set_client_info( to_char( x_pkg.g_cnt ) );
end;
/


Then, in another session you can query v$session with:

select usename, module, action, client_info from v$session;

to watch the insert/update/delete progress...


Of course, you can use the same technique for long running pl/sql routines as
well, putting calls to dbms_application_info at the head and tail of routines
for example to watch them run...



>David
>
>On Mon, 06 Jul 1998 14:09:16 GMT, tkyte@us.oracle.com (Thomas Kyte) wrote:
>
>>A copy of this was sent to dshi@magpage.com (David Shi)
>>(if that email address didn't require changing)
>>On 6 Jul 1998 01:07:27 GMT, you wrote:
>>
>>>Is there a way to check how many rows processed of a sql statement? Thanks.
>>>
>>>David
>>
>>depends on the language.
>>
>>In C and Pro*C sqlca.sqlerrd[2] has it.
>>
>>In PL/SQL with 'static' sql, sql%rowcount after an insert/update/delete has it.
>>
>>In PL/SQL with 'dynamic' sql, dbms_sql.execute returns it as a value...
>>
>>
>> 
>>Thomas Kyte
>>tkyte@us.oracle.com
>>Oracle Government
>>Herndon VA
>> 
>>http://govt.us.oracle.com/    -- downloadable utilities
>> 
>>----------------------------------------------------------------------------
>>Opinions are mine and do not necessarily reflect those of Oracle Corporation
>> 
>>Anti-Anti Spam Msg: if you want an answer emailed to you, 
>>you have to make it easy to get email to you.  Any bounced
>>email will be treated the same way i treat SPAM-- I delete it.

 
Thomas Kyte
tkyte@us.oracle.com
Oracle Government
Herndon VA
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.