Home » SQL & PL/SQL » SQL & PL/SQL » Capture NO_DATA_FOUND from refcursor
Capture NO_DATA_FOUND from refcursor [message #577020] Mon, 11 February 2013 22:27 Go to next message
newbiefm2012
Messages: 7
Registered: December 2012
Junior Member
I am returning a refcursor as OUT parameter in my stored procedure. I would like to capture a no data found for the refcursor. Is there a way I can raise the exception without compromising the performance?

I have tried the below options that are not working.
1. If I run a SELECT query to check for records and then OPEN the refcursor for that SELECT, then it takes a performance hit as I am reading the table twice.
2. I can FETCH a refcursor into a table type and check the count in the table to raise exception.
But once I fetch from a refcuror, the data is gone. So, this option does not work either.
Re: Capture NO_DATA_FOUND from refcursor [message #577022 is a reply to message #577020] Tue, 12 February 2013 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19340
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cursors don't raise NO-DATA-FOUND. If they find "nothing", they set the %FOUND cursor attribute to FALSE. Therefore, have a look at cursor attributes.
Re: Capture NO_DATA_FOUND from refcursor [message #577025 is a reply to message #577022] Tue, 12 February 2013 00:51 Go to previous message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
%FOUND is assigned after the first fetch so it does not solve OP's problem which does not want to fetch.

@newbiefm2012,

I don't see where is the problem: you open the cursor and pass it to the caller, the caller have to treat the rows until it reaches the end, so with or without rows it is the same thing.
Note: Littlefoot is right when he says that a cursor do not raise NO_DATA_FOUND and you have to check %FOUND attribute instead.

Regards
Michel

[Updated on: Tue, 12 February 2013 00:52]

Report message to a moderator

Previous Topic: Copy data from one table to another- Oracle
Next Topic: Parallel Process
Goto Forum:
  


Current Time: Thu Jul 31 07:56:25 CDT 2014

Total time taken to generate the page: 0.11256 seconds