RE: a pl/sql question

From: Davey, Alan <Alan.Davey_at_encodasystems.com>
Date: Fri, 30 Apr 2004 08:35:38 -0600
Message-ID: <7487761A46884449834CAD636919C07B0120CEBB@nycexc02.Denver.cjds.com>

An update that doesn't update any records is not an exception error.

Trying to select multiple record values or no record values when you expect to always get one and only one value is an exception, so that logic won't work in this situation.

Try this:
SQL> ed
Wrote file afiedt.buf

  1 declare x number;
  2 begin
  3 select client_id into x
  4 from ag_client
  5 where client_id = -1;
  6 if sql%notfound
  7 then dbms_output.put_line('No data found.');   8 end if;
  9 exception
10 when others
11 then dbms_output.put_line('Exception: No data found.'); 12* end;
SQL> / PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Exception: No data found.

It will never hit the if sql%notfound.

Alan Davey
Tech Lead
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458

-----Original Message-----
From: Freeman, Donald [mailto:dofreeman_at_state.pa.us] Sent: Friday, April 30, 2004 10:16 AM
To: Oracle-L (E-mail)
Subject: a pl/sql question

PINE-DFREEMAN> BEGIN
  2 UPDATE emp
  3 SET saL =3D100
  4 WHERE ename =3D 'POKEY';
  5 IF SQL%NOTFOUND THEN
  6 dbms_output.put_line('POKEY NOT FOUND');   7 END IF;
  8 END;
  9 /

POKEY NOT FOUND
PL/SQL procedure successfully completed.

I got the method from Scott Urman's Oracle 8i Advanced PL/SQL = Programming Book page 5. I am by no
means the world's best PL/SQL programmer, or even the best within 100 = feet of where I am sitting <g>
but this particular event can be handled without an exception.

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Anu
> Sent: Thursday, April 29, 2004 5:58 PM
> To: oracle-l_at_freelists.org
> Subject: RE: a pl/sql question

>=20
>=20

> I think you need exception here. It will error out if no row=20
> found. It will not go to :
> =20
> IF SQL%NOTFOUND THEN.=20

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

"This information in this e-mail is intended solely

for the addressee and may contain information which

is confidential or privileged.  Access to this

e-mail by anyone else is unauthorized. 

If you are not the intended recipient, or believe
 that you have received this communication in

error, please do not print, copy, retransmit,

disseminate, or otherwise use the information.

Also, please notify the sender that you have

received this e-mail in error, and delete the

copy you received."


"This information in this e-mail is intended solely for the addressee and
may contain information which is confidential or privileged.  Access to this
e-mail by anyone else is unauthorized.  If you are not the intended
recipient, or believe that you have received this communication in error,
please do not print, copy, retransmit, disseminate, or otherwise use the
information. Also, please notify the sender that you have received this
e-mail in error, and delete the copy you received."

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Apr 30 2004 - 09:33:48 CDT