OraFAQ Forum: SQL & PL/SQL » exception prob.

Home » SQL & PL/SQL » SQL & PL/SQL » exception prob.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

exception prob. [message #9453]

Tue, 11 November 2003 06:44

bhavin shah
Messages: 11
Registered: November 2003

Junior Member

dear sirs,
presently i am very new to pl/sql and just started to learn pl/sql basics.
i have made a cursor below.

declare cursor my_first_cursor(itemcode in varchar2)
is select mr_matcode,mr_matdesc from mf_refbook
where mr_matcode=itemcode;
itemcode varchar2(9);
itemdesc ntdata.mf_refbook.mr_matdesc%TYPE;
begin
open my_first_cursor('003230003');
loop
fetch my_first_cursor into itemcode,itemdesc;
dbms_output.put_line(itemcode);
exit when my_first_cursor %notfound;
dbms_output.put_line(itemdesc);
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('YOU HAVE NOT THIS MATCODE IN REFBOOK');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' I CANT UNDER STAND');
close my_first_cursor;
end;
/

here itemcode 003230003 is not in refbook and no rows selected in that cursors but even though it can't go to exception no_data_found.. why..??

Report message to a moderator

Re: exception prob. [message #9454 is a reply to message #9453]

Tue, 11 November 2003 08:41

sverch
Messages: 582
Registered: December 2000

Senior Member

1.Close your cursor before exception, not after.
2. Instead of coding your logic in the exception,
use cursor attribute:
IF my_first_cursor%NOTFOUND
THEN
DBMS_OUTPUT.PUT_LINE('YOU HAVE NOT THIS MATCODE IN REFBOOK');
ELSE...
END IF;

Report message to a moderator

Re: exception prob. [message #9455 is a reply to message #9453]

Tue, 11 November 2003 23:53

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Using an explicit cursor will not raise a NO_DATA_FOUND, ever... . You'll have to raise it yourself or use a 'SELECT INTO...' construction:

Select mr_matcode,mr_matdesc 
  Into itemcode, itemdesc
  From mf_refbook
 Where mr_matcode = itemcode;

Now a no_data_found is being thrown when there's no match. And you can trap a too_many_rows too, in case a duplicate entry would be found. I prefer a select into over a cursor because I can control it entirely. Of course a SELECT INTO is only suitable if I perform a lookup of one record only, in case of more than one record I use a cursor for loop.

Sverch is right, if you can do it otherwise and it is a normal flow, you wouldn't program an exception. You'd still want some exception handling of course.

But on the other hand, if it is an exceptional situation and the normal processing needs to be interrupted, go for the exception handling.

Some additional remarks:
1. Why a loop? You fill the same variables over and over, so only the last fetch is being put in the variables.

2. A cursor loop is more commonly used like this:

For rec_variable In your_cursor
Loop
  v_itemcode := rec_variable.mr_matcode;
  v_itemdesc := rec_variable.mr_matdesc;
End Loop;

That way you won't be needing an OPEN, FETCH, CLOSE construction. And, you can evaluate v_itemcode, see if it's filled. And you don't need to declare the rec_variable (which is always of your_cursor%TYPE).

3. Your naming is somewhat confusing. You create a cursor parameter and a variable with the same name. Try to avoid that.

And here's a useful link. The website (asktom.oracle.com) is one to keep. Very useful if you're looking for clear answers to your PL/SQL and SQL questions. Just search...

MHE

Report message to a moderator

Previous Topic:	ora 1536
Next Topic:	how to pick only duplicate members

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:37:12 CDT 2024