Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Question Unanswered: Trace NO_DATA_FOUND when using refcursors

    Dear Oracle Gurus,
    I am begineer in oracle. I need ur help in solving a problem.
    I need to know how to catch a exception when no rows are found in executing an Stored Proc in Oracle. I used Refcursor to fetch the more number of rows.

    I tried this code
    CREATE OR REPLACE PROCEDURE ADVREP.sp_getavailableusers (
    rgnid t_users.region_id%TYPE,
    outcursor OUT sys_refcursor

    )
    IS
    BEGIN
    OPEN outcursor FOR
    SELECT user_type AS userid, (firstname || ' ' || lastname) AS username
    FROM t_users
    WHERE region_id NOT IN (rgnid);

    WHEN outcursor%NOTFOUND then
    raise_application_error (-20001,
    'No Rows found:' || SQLERRM (SQLCODE)
    );



    EXCEPTION WHEN NO_DATA_FOUND
    THEN
    raise_application_error (-20001,
    'No Rows found:' || SQLERRM (SQLCODE)
    );


    END;
    /

    If i execute this SP by the following code


    If I execute this SP using this code
    var result refcursor
    execute sp_getavailableusers(20,:result);
    print result


    I get output like

    PL/SQL procedure successfully completed.
    Cursor returned no data.



    Please help me how to trace the exception. Let me know where i go wrong.

    Please help!!!!!!!

    Regards
    Pavithra

  2. #2
    Join Date
    May 2005
    Location
    Mumbai, India
    Posts
    2
    Try this

    ************************************************** ********
    /* Formatted on 2007/11/30 13:16 (Formatter Plus v4.8.6) */
    CREATE OR REPLACE PROCEDURE Sp_Getavailableusers (
    rgnid emp.deptno%TYPE,
    outcursor OUT sys_refcursor
    )
    IS
    BEGIN
    OPEN outcursor FOR
    SELECT empno AS userid, ename AS username
    FROM emp
    WHERE deptno NOT IN (rgnid);

    IF outcursor%ROWCOUNT = 0
    THEN
    RAISE_APPLICATION_ERROR (-20001, 'No Rows found:' || SQLERRM (SQLCODE));
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    RAISE_APPLICATION_ERROR (-20001, 'No Rows found:' || SQLERRM (SQLCODE));
    END;
    /
    ************************************************** ********

  3. #3
    Join Date
    Nov 2007
    Posts
    2

    Trace no data found

    I tried that code. but it raises exception even if records are present.

    I need to raise exception only when no records are present

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    When all attempts fail, you can still consult the documentation, found eg. online on http://tahiti.oracle.com/
    %ROWCOUNT Attribute: How Many Rows Fetched So Far?
    In short, without fetch, you will not know this. Fetches are supposed to be done by application, not by stored procedure. Why do you not move this logic into application? Fetching row and re-opening the cursor is quite strange.
    You could also use simple SELECT COUNT(*) query to get this info (limit it with ROWNUM = 1 not to perform full scan).

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You cannot know whether you will get results from the ref cursor before actually fetching the cursor . So the key is to check for the existence of results before actually opening the cursor. The most effective way to do so is to use an EXISTS clause (instead of a COUNT or so).

    So you could do something like this :
    Code:
    CREATE OR REPLACE PROCEDURE ADVREP.sp_getavailableusers(
        rgnid t_users.region_id%TYPE,
        outcursor OUT sys_refcursor) IS
        
        iUsersExist NUMBER;
    
    BEGIN
    
        SELECT CASE WHEN (EXISTS (SELECT NULL FROM t_users WHERE region_id <> rgnid)) THEN 1 ELSE 0 END
        INTO iUsersExist
        FROM DUAL;
        
        IF (iUsersExist = 0) THEN
            RAISE_APPLICATION_ERROR (-20001,'No Rows found');
        ELSE
            OPEN outcursor FOR
                SELECT user_type AS userid, (firstname || ' ' || lastname) AS username
                FROM t_users
                WHERE region_id <> rgnid:
        END IF;
    
    END sp_getavailableusers;
    /
    I replaced the NOT IN with <> as rgnid seems to be a single region code : you only need IN or NOT IN when you have a list of codes to check, otherwise always use = or <>.

    BTW why does this procedure need to know whether there are results or not ? Can't this be done at the caller level that will actually have to fetch the cursor, without the need for an exception ?

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •