Cursor and exception (Merged) [message #409520] |
Tue, 23 June 2009 00:40  |
ali560045
Messages: 25 Registered: May 2009
|
Junior Member |
|
|
i have the below requirement in my PL/SQL code
1) built a PL/SQL code using cursor to find the id number from table employee
2) IF no id found raise an EXCEPTION
Below is what i have tried but not getting the exception o/p
declare
cursor a_cur is select * from employee where id=09;
i a_cur%rowtype;
begin
for i in a_cur
LOOP
dbms_output.put_line(i.id);
END LOOP;
EXCEPTION
when NO_DATA_FOUND THEN
dbms_output.put_line(' Zero rows found');
END;
Can anyone please help me to get what i want ?
Thanks in advance
|
|
|
Re: Cursor and exception (Merged) [message #409528 is a reply to message #409520] |
Tue, 23 June 2009 01:04   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
This would certainly seem to be homework, is this the case?
Your code that you have tried will not raise a NO_DAT_FOUND error because all that you are doing is looping through the rows returned by the select in the declarions sections.
Instead, create a variable that you populate with the required id value and then use the SELECT INTO syntax in the executable section. If you do not know how to do this then you must go and look it up in your course notes or even better, the documentation (here as this is PL/SQL 101 and something that you really need to understand, therefore, us simply hand-feeding you the answer will do no good.
Feel free to post back with your attempts using the SELECT INTO syntax.
|
|
|
|
|
Re: Cursor and exception (Merged) [message #409808 is a reply to message #409789] |
Wed, 24 June 2009 02:54   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
What is there to explain delna?. The no_data_found error will not be raised and therefore your exception handler is pointless. I must admit, I'm not really sure what point it is that you are trying to make...
|
|
|
|
Re: Cursor and exception (Merged) [message #409837 is a reply to message #409834] |
Wed, 24 June 2009 04:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@Delna - try it and see. It's always better to run code that you post first - I still get caught outfrom time to time with things not working quite the way I thought.
|
|
|
|
Re: Cursor and exception (Merged) [message #409840 is a reply to message #409837] |
Wed, 24 June 2009 04:14   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
JRowbottom wrote on Wed, 24 June 2009 10:00 | @Delna - try it and see. It's always better to run code that you post first - I still get caught outfrom time to time with things not working quite the way I thought.
|
+1. Been caught out that way many a time. Generally now, if I can't test it, I don't post it.
|
|
|
|
Re: Cursor and exception (Merged) [message #409846 is a reply to message #409520] |
Wed, 24 June 2009 04:18  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
@JRowbottom sir,
Fully agree with you. But I was asking to know if, is there any reason for that behaviour of place of SELECT statement, as said by cookiemonster sir.
By the way, thanks all for their reply.
regards,
Delna
|
|
|