Re: SP for record set

In article <3c2a9f62$1_at_usenetgateway.com>, yewpc says...
>
>I have written a package as below to return a set if record. I have an
>expection for when NO_DATA_FOUND raise error. When i run the package
>with no matching record, Why it does not raise application error ? Does
>anyone can tell why ? Beside that, can anyone tell me is this the best
>way to write a sp for returning a record set ?
>
>CREATE OR REPLACE PACKAGE pg$ur_Get_User_Info_S IS
>
>TYPE udf_cur_Get_User_Role_List IS REF CURSOR;
>
>PROCEDURE sp$ur_Get_User_Role_List_S( pi_vch_Idsid IN
>R_EMPLOYEES.USER_IDSID%TYPE, pi_vch_Module_Name IN
>R_MODULE_EMPLOYEE_ROLES.MODULE_NAME%TYPE, pi_vch_Module_Site IN
>R_MODULE_EMPLOYEE_ROLES.MODULE_SITE%TYPE, po_cur_Get_User_Role_List OUT
>udf_cur_Get_User_Role_List );
>
>END pg$ur_Get_User_Info_S; / CREATE OR REPLACE PACKAGE BODY
>pg$ur_Get_User_Info_S IS PROCEDURE sp$ur_Get_User_Role_List_S(
>pi_vch_Idsid IN R_EMPLOYEES.USER_IDSID%TYPE, pi_vch_Module_Name IN
>R_MODULE_EMPLOYEE_ROLES.MODULE_NAME%TYPE, pi_vch_Module_Site IN
>R_MODULE_EMPLOYEE_ROLES.MODULE_SITE%TYPE, po_cur_Get_User_Role_List OUT
>udf_cur_Get_User_Role_List ) IS BEGIN OPEN po_cur_Get_User_Role_List FOR
>SELECT EMP.WWID,EMP.LONGID,EMP.FIRST_NAME,EMP.LAST_NAME,MER.ROLE_NAME
>FROM R_EMPLOYEES EMP ,R_MODULE_EMPLOYEE_ROLES MER ,R_ROLES ROL WHERE
>MER.MODULE_NAME = pi_vch_Module_Name AND MER.MODULE_SITE =
>pi_vch_Module_Site AND MER.WWID = EMP.WWID AND MER.ROLE_NAME =
>ROL.ROLE_NAME AND ROL.ROLE_LEVEL > (SELECT ROLE_LEVEL FROM R_EMPLOYEES
>EMP ,R_MODULE_EMPLOYEE_ROLES MER ,R_ROLES ROL WHERE MER.WWID = EMP.WWID
>AND MER.ROLE_NAME=ROL.ROLE_NAME AND MER.MODULE_NAME = pi_vch_Module_Name
>AND MER.MODULE_SITE = pi_Vch_Module_Site AND EMP.USER_IDSID =
>pi_vch_Idsid) ; EXCEPTION WHEN NO_DATA_FOUND THEN
>RAISE_APPLICATION_ERROR(-20000, 'No data found.'); END
>sp$ur_Get_User_Role_List_S; END pg$ur_Get_User_Info_S; /
>

the simple act of opening a cursor will not raise a NO_DATA_FOUND. In fact, you need to use an implicit cursor with a select into to get NO_DATA_FOUND:

  select x into y from t where 1=0;

that will raise NO_DATA_FOUND.

  open cursor for select x from t where 1=0;

will NEVER raise NO_DATA_FOUND. You have to FETCH from it:

   fetch cursor into y;
   if ( cursor%notfound ) then do something

and test it. The client who RECIEVES this cursor will fetch and find either data or no data.

This is the right way to return result sets from stored procedures.

>
>
>--
>Posted via dBforums
>http://dbforums.com
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp