Re: Increment at counter

From: Roel Toledo <dontsendmailstome_at_bogusmail.com>
Date: Wed, 24 Oct 2001 18:12:20 GMT
Message-ID: <8wDB7.81335$WW.4273465@bgtnsc05-news.ops.worldnet.att.net>

"Allan Martin" <allan.martin_at_saic.com> wrote in message news:3bd6837c_at_cpns1.saic.com...
> Hi,
> I'm trying to insert into a number variable the amount of
> records that I insert into a table. ie. I want to increment the counter by
> 1 every time a row is inserted. I'd then display the amount at the end.
>
> I have tried this over and over again and have even bought a book from
> AMAZON to help me in the future.
>
> Does anyone know what to do as my book will take more that 20 days to
arrive
> from
> America?
>
> I've got what I had in mind below.
>
> Thanks a lot,
> Allan
>
> DECLARE
>
> v_counter NUMBER := 0;
>
> BEGIN
>
> insert into tbl1
> (v1,
> v2,
> v3)
> select distinct
> v1,
> v2,
> v3
> -- v_counter = v_counter + 1;
> from theTable;
>
> commit;
>
> dbms_output.put_line('1. || v_counter || ' records inserted.');
>
> END;
The quickest and best way to do it is by using SQL%ROWCOUNT as follows:

DECLARE
    v_rows_inserted NUMBER := 0;
BEGIN

     insert into tbl1
         (v1,
          v2,
          v3)
    select distinct
          v1,
          v2,
          v3

   from theTable;
   v_rows_inserted := SQL%ROWCOUNT;
   commit;
   dbms_output.put_line('1. || v_rows_inserted || ' records inserted.');   END; Hth,

Roel Toledo Received on Wed Oct 24 2001 - 13:12:20 CDT