-
NO_DATA_FOUND Exception handling
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;
/
Last edited by DesiRookie; 08-30-2005 at 07:51 PM.
-
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?
-
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
-
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.
-
Thanks, gamyers. I understood it now.
Regards,
Desi Rookie
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|