Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Seeing results of stored procedure

Re: Seeing results of stored procedure

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 16 Jan 2003 07:23:44 -0800
Message-ID: <b06iq008cm@drn.newsguy.com>


In article <gqcb2vcrjt8e2tvfeti86ig842emv58a5s_at_4ax.com>, Harry says...
>
>On 15 Jan 2003 06:31:17 -0800, Thomas Kyte <tkyte_at_oracle.com> wrote:
>
>
>>
>>that you didn't open the ref cursor in the procedure (or you did but then closed
>>it before exiting the procedure).
>
>I tried that (I think), but still got the error. Maybe I've screwed
>something up in the procedure?? Here's the code:
>

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,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_LINE_1,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_LINE_2,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_MUNICIPALITY,
> MTB_PARISH_COUNTY.FIPS_CODE || '28',
> MTB_PARISH_COUNTY.PARISH_OR_COUNTY_DESC,
> MTB_STATE.STATE_CODE,
> MTB_STATE.STATE_DESC,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_ZIP,
> SUBJ_ITEM_LOCATION.NATIVE_LAND_FLAG,
> to_char(sysdate, 'YYYYMMDD')
>
> FROM
> AGENCY_INTEREST,
> AGENCY_INTEREST_ADDRESS,
> SUBJ_ITEM_LOCATION,
> MTB_MUNICIPALITY,
> MTB_PARISH_COUNTY,
> MTB_STATE
> WHERE
> AGENCY_INTEREST.MASTER_AI_ID = Facility_ID AND
> (AGENCY_INTEREST.INT_DOC_ID = 0 ) AND
> (AGENCY_INTEREST_ADDRESS.INT_DOC_ID = 0 ) AND
> (SUBJ_ITEM_LOCATION.INT_DOC_ID = 0 ) AND
> (SUBJ_ITEM_LOCATION.SUBJECT_ITEM_CATEGORY_CODE = 'AI' ) AND
> (AGENCY_INTEREST.MASTER_AI_ID =
> AGENCY_INTEREST_ADDRESS.MASTER_AI_ID(+) ) AND
> ((AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_MUNICIPALITY =
> mtb_municipality.MUNICIPALITY_DESC (+) ) AND
> (MTB_MUNICIPALITY.PARISH_OR_COUNTY_CODE =
>mtb_parish_county.PARISH_OR_COUNTY_CODE)) AND
> (AGENCY_INTEREST.MASTER_AI_ID =
> SUBJ_ITEM_LOCATION.MASTER_AI_ID(+) ) AND
> (AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_STATE_CODE =
> MTB_STATE.STATE_CODE(+) );
>

and that is it -- no record type, no fetch, no close.

The CLIENT will fetch
The CLIENT will close.

>(Preface - I wanted to return a complete record, even for the data we don't
>capture in our database, so that's why I have the record defined with
>default values in some fields, and the reason I did the FETCH INTO).
>
>{PACKAGE}
>
>CREATE OR REPLACE package Get_Facility as
>
> TYPE facility_rec IS RECORD
> (FacilityRegistryIdentifier VARCHAR2 (12) := '',
> FacilitySiteName agency_interest.master_ai_name%type,
> FacilitySiteTypeName VARCHAR2 (20) := 'Stationary',
> FederalFacilityIndicator VARCHAR2 (1) := '',
> TribalLandIndicator subj_item_location.native_land_flag%type,
> CongressionalDistrictNumber VARCHAR2 (2) := '',
> LegislativeDistrictNumber VARCHAR2 (2) := '',
> HUCCode VARCHAR2 (8) := '',
> LocationAddressText agency_interest_address.physical_address_line_1%type,
> SupplementalLocationText
>agency_interest_address.physical_address_line_2%type,
> LocalityName agency_interest_address.physical_address_municipality%type,
> CountyStateFIPSCode VARCHAR2 (5) := '',
> CountyName mtb_parish_county.parish_or_county_desc%type,
> StateUSPSCode mtb_state.state_code%type,
> StateName mtb_state.state_desc%type,
> CountryName VARCHAR2 (44) := 'USA',
> LocationZIPCode agency_interest_address.physical_address_zip%type,
> LocationDescriptionText VARCHAR2 (256) := '',
> DataSourceName VARCHAR2 (25) := 'MS State Master File',
> LastReportedDate VARCHAR2 (8),
> stateFacilitySystemAcronymName VARCHAR2 (20) := 'ENSITE',
> stateFacilityIdentifier agency_interest.master_ai_id%type);
>
> TYPE cur_Get_Fac_By_ID is REF CURSOR RETURN facility_rec;
>
> PROCEDURE Get_Facility_By_ID (v_Facility_in IN VARCHAR2,
> facility_out_cur OUT cur_Get_Fac_By_ID);
>
>END Get_Facility;
>/
>
>
>And here's the package body:
>
>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
>
>CURSOR cur_GetFacility_ByID (Facility_ID IN NUMBER) is
>
> SELECT AGENCY_INTEREST.MASTER_AI_ID,
> AGENCY_INTEREST.MASTER_AI_NAME,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_LINE_1,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_LINE_2,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_MUNICIPALITY,
> MTB_PARISH_COUNTY.FIPS_CODE || '28',
> MTB_PARISH_COUNTY.PARISH_OR_COUNTY_DESC,
> MTB_STATE.STATE_CODE,
> MTB_STATE.STATE_DESC,
> AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_ZIP,
> SUBJ_ITEM_LOCATION.NATIVE_LAND_FLAG,
> to_char(sysdate, 'YYYYMMDD')
>
> FROM
> AGENCY_INTEREST,
> AGENCY_INTEREST_ADDRESS,
> SUBJ_ITEM_LOCATION,
> MTB_MUNICIPALITY,
> MTB_PARISH_COUNTY,
> MTB_STATE
> WHERE
> AGENCY_INTEREST.MASTER_AI_ID = Facility_ID AND
> (AGENCY_INTEREST.INT_DOC_ID = 0 ) AND
> (AGENCY_INTEREST_ADDRESS.INT_DOC_ID = 0 ) AND
> (SUBJ_ITEM_LOCATION.INT_DOC_ID = 0 ) AND
> (SUBJ_ITEM_LOCATION.SUBJECT_ITEM_CATEGORY_CODE = 'AI' ) AND
> (AGENCY_INTEREST.MASTER_AI_ID =
> AGENCY_INTEREST_ADDRESS.MASTER_AI_ID(+) ) AND
> ((AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_MUNICIPALITY =
> mtb_municipality.MUNICIPALITY_DESC (+) ) AND
> (MTB_MUNICIPALITY.PARISH_OR_COUNTY_CODE =
>mtb_parish_county.PARISH_OR_COUNTY_CODE)) AND
> (AGENCY_INTEREST.MASTER_AI_ID =
> SUBJ_ITEM_LOCATION.MASTER_AI_ID(+) ) AND
> (AGENCY_INTEREST_ADDRESS.PHYSICAL_ADDRESS_STATE_CODE =
> MTB_STATE.STATE_CODE(+) );
>
> 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.FacilitySiteName,
> facility_out_rec.LocationAddressText,
> facility_out_rec.SupplementalLocationText,
> facility_out_rec.LocalityName,
> facility_out_rec.CountyStateFIPSCode,
> facility_out_rec.CountyName,
> facility_out_rec.StateUSPSCode,
> facility_out_rec.StateName,
> facility_out_rec.LocationZIPCode,
> facility_out_rec.TribalLandIndicator,
> facility_out_rec.LastReportedDate;
>
>CLOSE cur_GetFacility_ByID;
>
>END Get_Facility_By_ID;
>
>END Get_Facility;
>/
>
>
>And the attempt to execute:
>
>SQL> variable facility_out_cur REFCURSOR;
>SQL> exec Get_Facility.Get_Facility_By_ID(2290,:facility_out_cur);
>
>PL/SQL procedure successfully completed.
>
>SQL> print :facility_out_cur;
>ERROR:
>ORA-24338: statement handle not executed
>
>
>SP2-0625: Error printing variable "facility_out_cur"
>
>
>Thanks,
>Harry

--
Thomas Kyte (tkyte@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 
Received on Thu Jan 16 2003 - 09:23:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US