NO_DATA_FOUND Exception handling

[DesiRookie]

I have the following proc which should return me a cursor. If success, the output paramter po_ReturnCode should be 0 and when no data is found it should be -1. For any other exception it should be -999. But I get a value of 0 even when there is no data. I was able to do it by couting the number of rows and making sure the rowcount is > 0 before selecting it to a cursor. But I was wondering if there is a way to avoid that extra step and do it in a more efficient way. Any help is appreciated.

Thanks,
Desi Rookie

CREATE OR REPLACE PROCEDURE GET_LIST
(
po_returnCode OUT NUMBER,
po_List OUT REFCURSOR
)

AS

BEGIN
po_returncode := 0;

BEGIN
OPEN po_List
FOR
SELECT e.Employee_ID, e,Employee_Name
FROM EMPLOYEE e
WHERE e.Employee_ID > 100;

EXCEPTION
WHEN NO_DATA_FOUND THEN
po_returnCode := -1;
WHEN OTHERS THEN
po_returnCode := -999;
END;

END; -- GET_LIST;
/

[Rob Vollman]

I see two typos in there, shouldn't part of it be:

SELECT e.Employee_ID, e.Employee_Name
FROM EMPLOYEE e

?

And what is MyPkg.Result1?

[DesiRookie]

Rob,

Thanks for noticing. I have modified the posting. My original proc is a totally different one. I tried to simplify it for this posting, good enough to convey what I need. The output parameter is a REFCURSOR that holds the result set.

Thanks,
Desi Rookie

[gamyers]

NO_DATA_FOUND will only get raised when a fetch is made, not from simply opening the cursor. It should probably be picked up by the program using the cursor.

[DesiRookie]

Thanks, gamyers. I understood it now.

Regards,
Desi Rookie