Re: Seeing results of stored procedure

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:

(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 Received on Wed Jan 15 2003 - 13:22:09 CST