error handling for ref cursors returning procedures

Hi Gurus

I have this procedure that returns a ref cursor.. the cursor is a simple select statement. The problem is that if no rows are selected then i cannot trap the error in the exception handler in no_data_found, nor can i handle it through cursor attributes like cur%notfound. the cur%rowcount gives me 0 regardless of the number of rows returned by the cursor.. Pleae could anyone tell me how to handle for no rows selected in the procedure..

the procedure goes this way.. thanks in advacne.

• siddharth

           (P_employee_id                IN    
employee.employee_id%TYPE,
            P_employee                   OUT    lt_sid,
            P_errmsg                    OUT    VARCHAR2,
            P_errno                     OUT    NUMBER)

        P_errno     :=  0;
        OPEN P_employee FOR 
            SELECT  *
            FROM    employee
            WHERE   1 =1
            AND     employee_id = P_employee_id;
            

            DBMS_OUTPUT.PUT_LINE('SIDDDDDDDDDDDD 

            IF P_employee%NOTFOUND
            THEN
                DBMS_OUTPUT.PUT_LINE('not found');
                P_errno     :=  1;
                P_errmsg    := 'No data avaliable -
get_available_items - 0.5 ';
            END IF;

            IF P_employee%FOUND
            THEN
                DBMS_OUTPUT.PUT_LINE('found');
                P_errno     :=  1;
                P_errmsg    := 'No data avaliable -
get_available_items - 0.4 ';
            END IF;


            IF P_employee%ROWCOUNT >= 1
            THEN
                DBMS_OUTPUT.PUT_LINE('rowcount > 0');
                P_errno     :=  1;
                P_errmsg    := 'No data avaliable -
get_available_items - 0.3 ';
            ELSE
                DBMS_OUTPUT.PUT_LINE('else rowcount');
            END IF;             

            IF P_employee%ISOPEN
            THEN
                DBMS_OUTPUT.PUT_LINE('is open');
                P_errno     :=  1;
                P_errmsg    := 'No data avaliable -
get_available_items - 0.2 ';
            ELSE
                DBMS_OUTPUT.PUT_LINE('else is open');
            END IF;             

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            P_errno     :=  1;
            P_errmsg    := 'No data avaliable - get_available_items -

        WHEN OTHERS THEN
            P_errno     :=  1;
            P_errmsg    := 'Error - get_available_items - 0.0 '

END sid_pk;
/

show errors Received on Thu Jul 19 2001 - 21:12:56 CEST