Re: SP for record set

In article <3c2c0d33$1_at_usenetgateway.com>, yewpc says...
>
>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
>?

the select into is an IMPLICIT cursor. My one example was implicit.

If you ever call OPEN or CLOSE or FETCH, you are dealing with an EXPLICIT cursor.

>this mean i don't have to issue a close cursor right ?

you do not close IMPLICIT cursors.

you MUST (should) close explicit cursors eventually

> All the while i
>thought this is explicit cursor where i have to define the cursor, fetch
>and close it.
>

it is -- your code IS an explicit cursor.

> 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 ?
>

your code regarding the cursor is just fine, you have a stored procedure that returns a result set.

the error in logic is this "you cannot tell the caller that the cursor returns NO DATA". You do not know that (not until you fetch but if YOU fetch, they won't be able to!).

The CALLER of this procedure will have to look for NODATAFOUND when they themselves fetch.

>Thank you regards, ypc
>
>
>
>--
>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