SP for record set

From: yewpc <member_at_dbforums.com>
Date: 26 Dec 2001 23:11:14 -0500
Message-ID: <3c2a9f62$1@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:11:14 CST