Re: Seeing results of stored procedure

From: Harry Boswell <hboswel1_at_bellsouth.net>
Date: Thu, 16 Jan 2003 15:15:25 -0600
Message-ID: <h68e2vk6eo82ip7hkbuk9ldnug21c3mg86@4ax.com>

On 16 Jan 2003 07:23:44 -0800, in comp.databases.oracle.misc you wrote:

>
>you closed the cursor -- just as I said. Your code is:
>
>
>OPEN cur_GetFacility_ByID (v_Facility_in);
>FETCH cur_GetFacility_ByID <<<===
>CLOSE cur_GetFacility_ByID; <<<===
>
>
>two points -- if you FETCH from the cursor -- the client getting the cursor
>won't get squat! You already fetched it -- so even if you remove the close --
>you still won't get anything.
>
>Your code should just be:
>
>
>
>open cur_Get_Fac_By_ID
> for
>> SELECT AGENCY_INTEREST.MASTER_AI_ID,
>> AGENCY_INTEREST.MASTER_AI_NAME,

>
>and that is it -- no record type, no fetch, no close.
>
>The CLIENT will fetch
>The CLIENT will close.
>
>

OK, I'm more than a little confused now.

I thought the necessary steps in the package body were:

CREATE OR REPLACE PACKAGE BODY Get_Facility AS  

PROCEDURE Get_Facility_By_ID (v_Facility_in IN VARCHAR2,

                           facility_out_cur OUT cur_Get_Fac_By_ID) as

• declare the cursor???? CURSOR cur_GetFacility_ByID (Facility_ID IN NUMBER) is SELECT AGENCY_INTEREST.MASTER_AI_ID,

--then declare the output record????
 facility_out_rec facility_rec;

BEGIN
OPEN cur_GetFacility_ByID (v_Facility_in);

FETCH cur_GetFacility_ByID

   INTO

	facility_out_rec.stateFacilityIdentifier,
        .
        .
	facility_out_rec.LastReportedDate;

(so that I was specifying where the output should go)

CLOSE cur_GetFacility_ByID;

END Get_Facility_By_ID;

END Get_Facility;

Are you saying forget the cursor definition at the beginning of the procedure? Or just include the entire Select with an INTO clause in the cursor declaration? What would follow the BEGIN clause then?

Sorry if I'm overlooking something obvious - I haven't used cursors much, and ref cursors not at all.

Thanks,
Harry Received on Thu Jan 16 2003 - 15:15:25 CST