Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor Exception
Ref Cursor Exception [message #36426] Tue, 27 November 2001 09:19 Go to next message
Tex
Messages: 2
Registered: November 2001
Junior Member
Help! Does anyone know if it is possible to tell from a Open Cursor if any rows will be returned. I'm working on ASP App that calls a package with cursor ref that does a select on a few tables. I need to know before I return the cursor ref to ASP if any rows will be returned. If no data is found I need to know. Below is an example of what my code is doing. The exception never gets raised rather rows are returned or not.

Package PKG_A
IS
TYPE cusor_ref IS REF CURSOR;
Procedure sp_get_info (v_cur_out OUT cusor_ref, vin_dept_id in varchar2);
End PKG_A;
/

PACKAGE Body PKG_A
IS
Procedure sp_get_info (v_cur_out OUT cusor_ref, vin_dept_id in varchar2)
IS
Begin
OPEN v_cur_out FOR
SELECT * FROM EMP, DEPT
WHERE (EMP.DEPT = DEPT.DEPT) and DEPT.DEPT = vin_dept_id;

EXCEPTION
WHEN DATA_NOT_FOUND THEN
p_errorcode := 9999;
WHEN OTHERS THEN
p_errorcode := SQLCODE;
END sp_get_info;

END;

----------------------------------------------------------------------
Re: Ref Cursor Exception [message #36428 is a reply to message #36426] Tue, 27 November 2001 10:56 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
get count of records using same query before opening cursor.

----------------------------------------------------------------------
Re: Ref Cursor Exception [message #36431 is a reply to message #36428] Tue, 27 November 2001 12:32 Go to previous message
Tex
Messages: 2
Registered: November 2001
Junior Member
Thanks for the suggestion. I assume that capturing exceptions of a cursor ref is not possible?

----------------------------------------------------------------------
Previous Topic: Re: Arrays of Records ?
Next Topic: Re: How to Retrieve CLOB withADODB Command Parameter...?
Goto Forum:
  


Current Time: Sat Mar 28 19:56:27 CDT 2020