Re: How to get "number_of_affected_rows" after "insert ... select..."

From: <michael_bialik_at_my-deja.com>
Date: Fri, 05 Nov 1999 22:27:27 GMT
Message-ID: <7vvlkb$cj8$1@nnrp1.deja.com>

Hi.

Use SQL%ROWCOUNT immediatly after your insert statement : INSERT ....
IF SQL%ROWCOUNT > 1 THEN
...
ELSE
...
END IF; HTH. Michael.

In article <7vvgha$8mi$1_at_nnrp1.deja.com>, gmei_at_my-deja.com wrote:
> Hi:
>
> I have a procedure in an oracle package which does something like
>
> insert into table1
> (col1,
> col2)
> select
> val1,
> val2
> from tableA
> where tableA.colA = 'something';
>
> This can insert more than one rows in table1, I want to know how many
> rows inserted in table1 without doing another "select count(*) .."
query
> so I can determine if the sql runs successfully.
>
> Is there a "global" variable in oracle which will return the
> "number_of_affected_rows" after an executed sql statement?
>
> I am not using dynamic sql and I thought "RETURNING" clause only can
> return single row.
>
> Any suggestions?
>
> Thanks.
>
> Guang
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 16:27:27 CST