try formatting the code so one can read it.
Jim
"yewpc" <member_at_dbforums.com> wrote in message
news:3c2a9f62$1_at_usenetgateway.com...
> 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; /
>
>
>
> --
> Posted via dBforums
> http://dbforums.com
>
Received on Wed Dec 26 2001 - 22:32:52 CST
