Home » SQL & PL/SQL » SQL & PL/SQL » Do we need to raise exception while using ref cursor? (Oracle)
Do we need to raise exception while using ref cursor? [message #319577] Mon, 12 May 2008 05:13 Go to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Do we need to raise exception while using ref cursor?
create or replace procedure get_count
     (p_deptno in number,
      p_job    in char,
      c1       out sys_refcursor
      )
    is
    begin
      open c1 for
        select e.deptno, count(*)
         from emp e
         where e.deptno = p_deptno
           and e.job = NVL(p_job, e.job)       --> JOB is optional
         group by e.deptno;
   exception
   when NO_DATA_FOUND then
   null;
   when OTHERS then
   null; 
   end;
   /


-------------------------------------------------------
Re: Do we need to raise exception while using ref cursor? [message #319582 is a reply to message #319577] Mon, 12 May 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Raise? You mean handle?
Your exception clause MUST be removed.

"when OTHERS then null;" is a BUG.
"when NO_DATA_FOUND" is useless as it will never happen. NO_DATA_FOUND is raised when you try to retrieve (fetch) data not at open time.

Regards
Michel

Re: Do we need to raise exception while using ref cursor? [message #319589 is a reply to message #319582] Mon, 12 May 2008 05:43 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member

Thank you very much for your quick reply Michel.As always, you are always helpful..

What did you mean when u said -"when OTHERS then null;" is a BUG.
should not we use this exception to handle errors? If not why?
Please kindly explain.




Michel Cadot wrote on Mon, 12 May 2008 05:25
Raise? You mean handle?
Your exception clause MUST be removed.

"when OTHERS then null;" is a BUG.
"when NO_DATA_FOUND" is useless as it will never happen. NO_DATA_FOUND is raised when you try to retrieve (fetch) data not at open time.

Regards
Michel



Re: Do we need to raise exception while using ref cursor? [message #319591 is a reply to message #319589] Mon, 12 May 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "when OTHERS then null;" mean?
It means whatever happen I don't care and I don't want to know, anytime I return "SUCCESS".
You can or not open the cursor, it is the same, no error.
And this is a simple case think if you do this in a complex procedure that does many things.

WHEN OTHERS should be use in at most one purpose: logging and then RAISE should be the next statement.

Regards
Michel

Re: Do we need to raise exception while using ref cursor? [message #319608 is a reply to message #319591] Mon, 12 May 2008 06:39 Go to previous messageGo to next message
NIRVANA
Messages: 11
Registered: May 2008
Junior Member
Michel Cadot wrote on Mon, 12 May 2008 05:51
What does "when OTHERS then null;" mean?
It means whatever happen I don't care and I don't want to know, anytime I return "SUCCESS".
You can or not open the cursor, it is the same, no error.
And this is a simple case think if you do this in a complex procedure that does many things.

WHEN OTHERS should be use in at most one purpose: logging and then RAISE should be the next statement.

Regards
Michel




Thank you very much!
Re: Do we need to raise exception while using ref cursor? [message #319778 is a reply to message #319608] Tue, 13 May 2008 00:47 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi Nirvana,

If you click the reply button for a specific reply, the id of that reply will be displayed in your reply (the "is a reply to" hyperlink).
There is no need to quote a complete reply, since it is there for all to see.
Previous Topic: Using CASE within a CASE within a CASE - does this make sense?
Next Topic: Convert string to date format
Goto Forum:
  


Current Time: Sun Dec 11 00:28:01 CST 2016

Total time taken to generate the page: 0.23743 seconds