Re: Getting a count on the number of affect rows

From: Aina Blix <ainab_at_nextel.no>
Date: Tue, 29 Sep 1998 14:47:00 +0200
Message-ID: <3610D6C4.C8F55EDB@nextel.no>

I think something like this might be faster:

CREATE or REPLACE PROCEDURE update_rows

  (v_field                 IN number,

   v_rows_updated OUT varchar2)
IS
BEGIN
   UPDATE table_name
   SET table_field = value
   WHERE some_field = v_field;
   v_rows_updated := TO_CHAR(SQL%ROWCOUNT) || ' rows updated.'; END update_rows;

GHouck wrote:

> danalynn_at_my-dejanews.com wrote:
> >
> > Hello,
> >
> > I was wondering if there is a way to get the number of rows affected by a SQL
> > (insert/update) command (not in PL/SQL). I know you can use @@rowcount in
> > MSSQL, is there a similar variable in Oracle that I can use?
> >
> > Thanks.
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
> You could create a Trigger that simply counts in
> separate table when the table of interest experiences
> an INSERT or UPDATE.
>
> Something like:
>
> CREATE TABLE MYTABLE_COUNTER ( COUNTER );
>
> CREATE OR REPLACE TRIGGER MYTABLE_COUNTER
> BEFORE UPDATE ON MYTABLE
> FOR EACH ROW
> BEGIN
> UPDATE MYTABLE_COUNTER SET COUNTER=COUNTER+1;
> END;
>
> Probably not the most efficient method though,
> but relatively simple.
>
> Yours,
>
> Geoff Houck
> systems hk
> hksys_at_teleport.com
> http://www.teleport.com/~hksys

-Aina Blix Received on Tue Sep 29 1998 - 07:47:00 CDT