Re: error handling for ref cursors returning procedures
Date: 20 Jul 2001 01:57:35 -0700
Message-ID: <99f3c2af.0107200057.11b9e517_at_posting.google.com>
sid007s_at_hotmail.com (siddharth) wrote in message news:<d9382fc1.0107191112.5690c5fc_at_posting.google.com>...
> 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
>
> CREATE OR REPLACE PACKAGE BODY sid_pk AS
>
>
> PROCEDURE get_available_items
> (P_employee_id IN
> employee.employee_id%TYPE,
> P_employee OUT lt_sid,
> P_errmsg OUT VARCHAR2,
> P_errno OUT NUMBER)
> IS
> BEGIN
> P_errno := 0;
> OPEN P_employee FOR
> SELECT *
> FROM employee
> WHERE 1 =1
> AND employee_id = P_employee_id;
>
>
> DBMS_OUTPUT.PUT_LINE('SIDDDDDDDDDDDD
> '||P_employee%ROWCOUNT);
>
> 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 -
> 0.1 ' ||SQLERRM;
>
> WHEN OTHERS THEN
> P_errno := 1;
> P_errmsg := 'Error - get_available_items - 0.0 '
> ||SQLERRM;
>
> END get_available_items;
>
>
> END sid_pk;
> /
>
> show errors
In my opinion, unless you fetch from a cursor, you'll never get to know whether the cursor actually picks up records or not.
Bhooshan Received on Fri Jul 20 2001 - 10:57:35 CEST