REPOST: Re: SP for record set

Thomas Kyte wrote:
> 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/http://dbforums.com
> >
> --
> Thomas Kyte (tkyte_at_us.oracle.com)
> http://asktom.oracle.com/http://asktom.oracle.com/ Expert one on one
> Oracle, programming techniques and solutions for Oracle. http://www.ama-
> zon.com/exec/obidos/ASIN/1861004826/http://www.amazon.com/exec/obidos/A-
> SIN/1861004826/ Opinions are mine and do not necessarily reflect those
> of Oracle Corp

Hi Thomas, You mean the example you give is made use of implicit cursor ? this mean i don't have to issue a close cursor right ? All the while i thought this is explicit cursor where i have to define the cursor, fetch and close it.

    Is it the way i coded my package does not use any type of cursor at     all ? or it is not the right way at all ? Actually, I got this     package from my colleage and they use this way to code their     packages for a project. Will it causes any problem ie performance,     memory lead for example ?

Thank you regards, ypc

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

========= WAS CANCELLED BY =======:
Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!uunet!ash.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r
From: yewpc <member_at_dbforums.com>
Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server
Subject: cmsg cancel <3c2c0d33$1_at_usenetgateway.com>
Control: cancel <3c2c0d33$1_at_usenetgateway.com>
Date: Mon, 31 Dec 2001 08:17:49 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.3c2c0d33$1_at_usenetgateway.com>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009790193 28060 166.102.15.34 (31 Dec 2001 09:16:33 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 31 Dec 2001 09:16:33 GMT
X-No-Archive: yes
Comment: Anarchy! Fuck You!
X-Commentary: I love NewsAgent 1.10, Sandblaster Build 74 (19 March 1999) and the Polaris Cancel Engine V. 6.1
X-Unacanc3l: yes

This message was cancelled from within Mozilla...not