Re: How can I receive number of rows inserted in PL/SQL?

Every sql statement in pl/sql block opens a cursor.. i think u know about it. This cursor can be checked in the block using its "handle"

   SQL. U can use SQL%ROWCOUNT(upd/del/ins) to get the number of affected rows just after the sql statement. begin
 > insert into t2(id) select id from t1;  > i:= SQL%ROWCOUNT;

      return i;
 > end p1;

Hope this helps.
Abhijith.

Christian Haberbosch wrote:
> How do I retrieve the number of rows inserted in PLSQL?
>
> When I Am executing an INSERT command via SQLPLUS, then I get a message
> like:
> SQL> x rows created in y seconds.
>
>
> Probably an easy question, but I cannot find it in the documentation.
> Thanks in advance for your comments,
> Christian.
>
> Example:
> SQL> create table t1 ( id number );
> SQL> create table t2 ( id number );
> SQL> INSERT INTO insert into t1 values ( 1 );
> SQL> INSERT INTO insert into t1 values ( 2 );
> SQL> INSERT INTO insert into t1 values ( 3 );
>
> Now, I want to copy the records from t1 to t2, and I am interested in
> the number of records copied. (But I do not want to make a second SQL
> statement like "select count(*) from t1")
>
> In SQL it is easy. Just
> SQL> insert into t2(id) select id from t1;
> SQL> 3 rows created in 0 seconds.
>
> and I know, that 3 records have been inserted.
>
> How can I do this in PLSQL?
>
> I want to have a function like:
> -- the function p1 copies all records from t1 to t2
> -- and returns how many rows have been inserted in t2
> create or replace function p1 return number
> is
> begin
> insert into t2(id) select id from t1;
> return ???;
> end p1;
>
>
>
Received on Mon Jun 23 2003 - 04:29:15 CDT