NO_DATA_FOUND Exception handling
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: NO_DATA_FOUND Exception handling

  1. #1
    Join Date
    Jun 2005
    Posts
    11

    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.

  2. #2
    Join Date
    Jun 2005
    Location
    Calgary, Alberta, Canada
    Posts
    9
    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?

  3. #3
    Join Date
    Jun 2005
    Posts
    11
    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

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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.

  5. #5
    Join Date
    Jun 2005
    Posts
    11
    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
  •  



Click Here to Expand Forum to Full Width