You have to declare a variable of type facility_out_rec and fetch into it
(which means that your select and the record fields have to be in the same
order).
"Harry Boswell" <hboswel1_at_bellsouth.net> wrote in message
news:gqo7tucanamsbkonqnj6rm5flu0r80oc8e_at_4ax.com...
> I'm having a problem with a stored procedure. The error I get on
compiling
> is "PLS-00330: invalid use of type name or subtype name", out of the FETCH
> INTO statement. It's flagging the
> "facility_out_rec.stateFacilityIdentifier". I haven't tried this before,
> but I thought this syntax was the proper way to return query values to
> particular fields in the defined output record. Here's the relevant parts
> of the code:
>
> CREATE OR REPLACE PROCEDURE GetFacilityByID (v_Facility_in IN VARCHAR2) as
>
> TYPE facility_out_rec IS RECORD
> (FacilityRegistryIdentifier VARCHAR2 (12) := '',
> FacilitySiteName agency_interest.master_ai_name%type,
> FacilitySiteTypeName VARCHAR2 (20) := 'Stationary',
> .
> .
> .
> .
> LastReportedDate VARCHAR2 (8),
> stateFacilitySystemAcronymName VARCHAR2 (20) := 'ENSITE',
> stateFacilityIdentifier agency_interest.master_ai_id%type);
>
> CURSOR cur_GetFacility_ByID (Facility_ID IN NUMBER) is
>
> SELECT AGENCY_INTEREST.MASTER_AI_ID,
> AGENCY_INTEREST.MASTER_AI_NAME,
> .
> .
> .
> 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_ADDRESS.PHYSICAL_ADDRESS_STATE_CODE =
> MTB_STATE.STATE_CODE(+) );
>
> BEGIN
>
> OPEN cur_GetFacility_ByID (v_Facility_in);
>
> FETCH cur_GetFacility_ByID
> INTO
> facility_out_rec.stateFacilityIdentifier,
> facility_out_rec.FacilitySiteName,
> .
> .
> .
> facility_out_rec.LastReportedDate;
>
> CLOSE cur_GetFacility_ByID;
>
> END GetFacilityByID;
>
>
>
> Thanks,
> Harry Boswell
Received on Thu Nov 14 2002 - 12:31:41 CST
