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.
CREATE OR REPLACE PROCEDURE GET_LIST
po_returnCode OUT NUMBER,
po_List OUT REFCURSOR
po_returncode := 0;
SELECT e.Employee_ID, e,Employee_Name
FROM EMPLOYEE e
WHERE e.Employee_ID > 100;
WHEN NO_DATA_FOUND THEN
po_returnCode := -1;
WHEN OTHERS THEN
po_returnCode := -999;
END; -- GET_LIST;
Last edited by DesiRookie; 08-30-2005 at 07:51 PM.
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.