Re: HOw many records were affected?

Sergey,

There's one small error in your code. Instead of

:n := sql%rowcount;

you should use

:n := :n + sql%rowcount;

otherwise, you always get 1 when you print n at the end.

Yong Huang
yhuang_at_indigopool.com

<sergey_s_at_my-deja.com> wrote in message news:8nkpuu$djt$1_at_nnrp1.deja.com...
> SQL> create table t (i number);
>
> Table created.
>
> SQL> insert into t values (1);
>
> 1 row created.
>
> SQL> var n number; <- This creates a global variable n in sqlplus
> SQL>begin
> 2 update t set i=13 where i=1;
> 3 :n := sql%rowcount; <- Number of affected rows assigned to n
> 4 end;
> 5 /
>
> PL/SQL procedure successfully completed.
>
> SQL> print n
>
> N
> ----------
> 1
>
> SQL>
>
>
> So, you need to assign sql%rowcount to a variable of your choice and do
> it inside a stored proc immediately after the execution of the desired
> statement.
>
> hth
>
> Sergey
>
>
> In article <8njova$700$1_at_nnrp1.deja.com>,
> jgotthelf_at_my-deja.com wrote:
> > Hi,
> >
> > I'm accessing Oracle via ODBC Passthrough - and after and UPDATE or
> > INSERT I need to find out how many records were updated. IN SQL
 Server
> > I do:
> >
> > update table_name set value = 42 where key_field = 11
> > select @@rowcount
> >
> > If the update was successfull, @@rowcount returns a 1, else it returns
> > a zero.
> >
> > How can I accomplish the same in Oracle?
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Aug 21 2000 - 14:34:22 CDT