Home » SQL & PL/SQL » SQL & PL/SQL » Cursor and exception (Merged)
Cursor and exception (Merged) [message #409520] Tue, 23 June 2009 00:40 Go to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 #409789 is a reply to message #409528] Wed, 24 June 2009 01:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello pablolee sir,
And what about this?

SQL>begin
  2   for i in (select *  from emp where deptno=9)
  3   loop
  4    dbms_output.put_line(i.deptno);
  5   end loop;
  6   dbms_output.put_line('End of Begin');
  7  exception
  8   when no_data_found then
  9    dbms_output.put_line(' Zero rows found');
 10  end;
 11  /
End of Begin

PL/SQL procedure successfully completed.

Please explain, if any rule

regards,
Delna
Re: Cursor and exception (Merged) [message #409795 is a reply to message #409789] Wed, 24 June 2009 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Without wanting to give away the answer completely, let's review the requirements:

1. use cursor to select id number from table employee
2. if employee id is not found raise an exception

Here are the questions:

Where do you raise an exception? You don't, in fact. Instead, you chose to handle the exception, that is not likely to be raised in the first place.

Does a cursor for-loop ever raise a NO_DATA_FOUND exception by itself?

Here's what I read from the assignment:
1. SELECT an employee based on its employee_id
2. FOUND -> return the id.
3. NOT FOUND -> RAISE. Don't return a message or anything, but RAISE the NO_DATA_FOUND.

This should get you on the way. Good luck!

MHE
Re: Cursor and exception (Merged) [message #409808 is a reply to message #409789] Wed, 24 June 2009 02:54 Go to previous messageGo to next message
pablolee
Messages: 2834
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 #409834 is a reply to message #409808] Wed, 24 June 2009 03:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
@pablolee sir,
Quote:

because all that you are doing is looping through the rows returned by the select in the declarions sections.



If I use that SELECT statement in executable section, then also its behaveour is same.
In this case, shouldn't it raise NO_DATA_FOUND exception?

and I didn't get what Maaher sir said.

regards,
Delna
Re: Cursor and exception (Merged) [message #409837 is a reply to message #409834] Wed, 24 June 2009 04:00 Go to previous messageGo to next message
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 #409838 is a reply to message #409520] Wed, 24 June 2009 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
FOR LOOPs never raise no_data_found by definition.
explicit cursors never raise no_data_found by definition.
SELECT INTO can raise no_data_found.
Re: Cursor and exception (Merged) [message #409840 is a reply to message #409837] Wed, 24 June 2009 04:14 Go to previous messageGo to next message
pablolee
Messages: 2834
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 #409841 is a reply to message #409838] Wed, 24 June 2009 04:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
delna.sexy wrote on Wed, 24 June 2009 10:56
and I didn't get what Maaher sir said.

cookiemonster added some clarification.
cookiemonster wrote on Wed, 24 June 2009 11:02
FOR LOOPs never raise no_data_found by definition.
explicit cursors never raise no_data_found by definition.
SELECT INTO can raise no_data_found.

MHE
Re: Cursor and exception (Merged) [message #409846 is a reply to message #409520] Wed, 24 June 2009 04:18 Go to previous message
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
Previous Topic: Sql Tuning
Next Topic: Creating procedure dynamically-Insufficient privilages
Goto Forum:
  


Current Time: Tue Dec 06 10:32:44 CST 2016

Total time taken to generate the page: 0.13637 seconds