RE: RE: SQL%ROWCOUNT with update statement

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Tue, 19 Dec 2000 07:52:46 -0500
Message-Id: <10715.124917@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C069BA.960D81B8
Content-Type: text/plain;

charset="iso-8859-1"

Hello Dick,

Thanks for your response. Yes, what you outlined below makes total sense.

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com] Sent: Monday, December 18, 2000 4:56 PM
To: Multiple recipients of list ORACLE-L Subject: Re:RE: SQL%ROWCOUNT with update statement

Lisa,

There is one and only one case I've seen where the implicit cursor attribute
returned an unexpected value. In this case it was not Oracle's fault, but the
developers. What he had was basically the following (shortened for brevity & to
save the list some work):

for a in (select <a_pile_of_columns> from <user_defined_table>) loop

if(a.<some_column> = <a_value>) then

        update <results_table>
      end if;

if(SQL%ROWCOUNT > 0) then .....
end if;
end loop

Care to guess what SQL%ROWCOUNT was referring to? It depended on whether or not
the update statement fired. Kind of weird, but if you read the PL/SQL manual
it's clear.

Dick Goulet

____________________Reply Separator____________________
Author: "Bala; Prakash" <prakash.bala_at_cingular.com>
Date:       12/18/2000 12:31 PM

Lisa, so far sql%rowcount has returned me the right values.

Prakash

-----Original Message-----
Sent: Monday, December 18, 2000 2:41 PM
To: Multiple recipients of list ORACLE-L

Hello all,

One of the developers here is having trouble using this cursor attribute. He swears up and down the statement is correct and should be updating rows. I tried to recreate his problem and wasn't able to. Below is an example of the type of code I'm referencing.

Has anyone ever seen the implicit cursor attributes screw up and return wrong values? I don't believe I ever have. Any responses are appreciated.

Thanks

SQL> l
  1 declare
  2 ln_rowcount NUMBER;
  3 ln_dummy DATE;
  4 BEGIN
  5 UPDATE BIN_LIST
  6 SET LAST_MOD_TMSTMP = SYSDATE;
  7 LN_ROWCOUNT := SQL%ROWCOUNT;
  8 DBMS_OUTPUT.PUT_LINE(ln_rowcount);   9 COMMIT;
10 LN_ROWCOUNT := SQL%ROWCOUNT;
11 DBMS_OUTPUT.PUT_LINE(ln_rowcount); 12
13 select sysdate into ln_dummy from dual; 14 LN_ROWCOUNT := SQL%ROWCOUNT;
15 DBMS_OUTPUT.PUT_LINE(ln_rowcount); 16
17
18* END;
SQL> /
3062
3062
1

PL/SQL procedure successfully completed.

SQL> Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174 
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com <http://www.qode.com>

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bala, Prakash
INET: prakash.bala_at_cingular.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

------_=_NextPart_001_01C069BA.960D81B8
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12">

<TITLE>RE: RE: SQL%ROWCOUNT with update statement</TITLE>
</HEAD>
<BODY>

Hello Dick,

Thanks for your response.  Yes, what you = outlined below makes total sense.

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319</FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>

C: 954.658.5849 <A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A>

"The information contained herein does not = express the opinion or position of Qode.com and cannot be attributed to = or made binding upon Qode.com."

-----Original Message----- From: dgoulet_at_vicr.com [<A = HREF=3D"mailto:dgoulet_at_vicr.com">mailto:dgoulet_at_vicr.com</A>]

<BR><FONT SIZE=3D2>Sent: Monday, December 18, 2000 4:56 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re:RE: SQL%ROWCOUNT with update =

statement

Lisa,

There is one and only one case = I've seen where the implicit cursor attribute returned an unexpected value.  In this case it = was not Oracle's fault, but the
 developers.  What he had was basically the = following (shortened for brevity & to save the list some work):

for a in (select <a_pile_of_columns> from = <user_defined_table>) loop
     if(a.<some_column> =3D = <a_value>) then 
         update = <results_table>
       end if;     if(SQL%ROWCOUNT > 0) then = .....

<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; end if;</FONT>
<BR><FONT SIZE=3D2>end loop</FONT>
</P>

Care to guess what SQL%ROWCOUNT was referring = to?  It depended on whether or not the update statement fired.  Kind of weird, but = if you read the PL/SQL manual
 it's clear.

Dick Goulet

<P><FONT SIZE=3D2>____________________Reply =
Separator____________________</FONT>

Author: "Bala; Prakash" = <prakash.bala_at_cingular.com> Date:       12/18/2000 = 12:31 PM

Lisa, so far sql%rowcount has returned me the right = values.

<BR><FONT SIZE=3D2>&nbsp;</FONT>
<BR><FONT SIZE=3D2>Prakash</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>Sent: Monday, December 18, 2000 2:41 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
</P>

Hello all,

One of the developers here is having trouble using = this cursor attribute.
 He swears up and down the statement is correct and = should be updating rows.
 I tried to recreate his problem and wasn't able = to.  Below is an example of
 the type of code I'm referencing.

Has anyone ever seen the implicit cursor attributes = screw up and return

<BR><FONT SIZE=3D2>wrong values?&nbsp; I don't believe I ever =
have.&nbsp; </FONT>
<BR><FONT SIZE=3D2>Any responses are appreciated. </FONT>

Thanks

<P><FONT SIZE=3D2>SQL&gt; l </FONT>
<BR><FONT SIZE=3D2>&nbsp; 1&nbsp; declare </FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; ln_rowcount NUMBER; </FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; ln_dummy DATE; </FONT>
<BR><FONT SIZE=3D2>&nbsp; 4&nbsp; BEGIN </FONT>
<BR><FONT SIZE=3D2>&nbsp; 5&nbsp; UPDATE BIN_LIST </FONT>
<BR><FONT SIZE=3D2>&nbsp; 6&nbsp; SET LAST_MOD_TMSTMP =3D SYSDATE; =
</FONT>
<BR><FONT SIZE=3D2>&nbsp; 7&nbsp; LN_ROWCOUNT :=3D SQL%ROWCOUNT; =
</FONT>
<BR><FONT SIZE=3D2>&nbsp; 8&nbsp; DBMS_OUTPUT.PUT_LINE(ln_rowcount); =
</FONT>
<BR><FONT SIZE=3D2>&nbsp; 9&nbsp; COMMIT; </FONT>
<BR><FONT SIZE=3D2>&nbsp;10&nbsp; LN_ROWCOUNT :=3D SQL%ROWCOUNT; =
</FONT>
<BR><FONT SIZE=3D2>&nbsp;11&nbsp; DBMS_OUTPUT.PUT_LINE(ln_rowcount); =
</FONT>
<BR><FONT SIZE=3D2>&nbsp;12 </FONT>
<BR><FONT SIZE=3D2>&nbsp;13&nbsp; select sysdate into ln_dummy from =

dual;

<BR><FONT SIZE=3D2>&nbsp;14&nbsp; LN_ROWCOUNT :=3D SQL%ROWCOUNT; =
</FONT>
<BR><FONT SIZE=3D2>&nbsp;15&nbsp; DBMS_OUTPUT.PUT_LINE(ln_rowcount); =
</FONT>
<BR><FONT SIZE=3D2>&nbsp;16 </FONT>
<BR><FONT SIZE=3D2>&nbsp;17 </FONT>
<BR><FONT SIZE=3D2>&nbsp;18* END; </FONT>
<BR><FONT SIZE=3D2>SQL&gt; / </FONT>
<BR><FONT SIZE=3D2>3062 </FONT>
<BR><FONT SIZE=3D2>3062 </FONT>
<BR><FONT SIZE=3D2>1 </FONT>
</P>

PL/SQL procedure successfully completed.

SQL>

<P><FONT SIZE=3D2>Lisa Rutland Koivu </FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator </FONT>
<BR><FONT SIZE=3D2>Qode.com </FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7 </FONT>
<BR><FONT SIZE=3D2>Suite G104 </FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319 </FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174 </FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>

C: 954.658.5849 <A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A> <<A = HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A>>

"The information contained herein does not = express the opinion or position
 of Qode.com and cannot be attributed to or made = binding upon Qode.com."

-- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Bala, Prakash</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: prakash.bala_at_cingular.com</FONT>
</P>

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

-----
 To REMOVE yourself from this mailing list, send an = E-Mail message
 to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in
 the message BODY, include a line containing: UNSUB = ORACLE-L
 (or the name of mailing list you want to be removed = from).  You may
 also send the HELP command for other information = (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: </FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: dgoulet_at_vicr.com</FONT>
</P>

SIZE=3D2>---------------------------------------------------------------=