I'm trying to create a function to return a result set defined by a package.
The function populates the Global Temporary table named: GttGetNameAddressList
This table is used when creating the result set to limit the results to the "CustomerNo" which is a column in this view: FwContractBalancesQueryV.
This is my first attempt at us SYS_REFCURSOR.
Here is my PL/SQL:
-- ============================================================================
-- Function: Fwtest
--
-- Return Code(s): NONE
-- ============================================================================
CREATE or replace FUNCTION Fwtest
(AsUserName IN varchar2,
AsPurchSale IN varchar2,
AsNameAndAddressId IN varchar2,
naa_result_set OUT SYS_REFCURSOR,
v_recordset OUT SYS_REFCURSOR
)
RETURN SYS_REFCURSOR
IS
v_result_set PackFwContractBalanceQuery.RcFwContractBalanceQuery;
BEGIN
lv_user_name := AsUserName;
lv_purch_sale := AsPurchSale;
lv_name_address_id := AsNameAndAddressId;
lv_get_naa := s1_NameAndAddress.NameAndAddressId%Type;
lv_full_name := s1_NameAndAddress.FullName%Type;
lv_parent_record_id := s1_NameAndAddress.ParentRecordId%Type;
lv_pass := number;
WHILE TRUE LOOP
FETCH naa_result_set INTO lv_get_naa,
lv_full_name,
lv_parent_record_id,
lv_pass;
EXIT WHEN naa_result_set%NOTFOUND;
INSERT INTO GttGetNameAddressList
VALUES (lv_get_naa,lv_full_name,lv_parent_record_id,lv_pass);
END LOOP;
OPEN v_result_set FOR
SELECT ActualApplied
FROM FwContractBalancesQueryV contract,
A1UserLocation A1User
WHERE contract.LocationId = A1User.PlcId
AND A1User.UserId = lv_user_name
AND (lv_purch_sale IS NULL OR
(lv_purch_sale IS NOT NULL AND
ContractTypeCode = lv_purch_sale
)
)
AND ((lv_name_and_address_id IS NULL) OR
((lv_name_and_address_id IS NOT NULL) AND
(CustomerNo IN
(SELECT NameAndAddressId
FROM GttGetNameAddressList
)
)
)
);
-- ============================================================
-- Process finished with SUCCESS
-- ============================================================
RETURN;
END;
/
It produces the following errors which I don't understand since the variables are declared:
Function FWTEST compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
15/4 PLS-00201: identifier 'LV_USER_NAME' must be declared
15/4 PL/SQL: Statement ignored
16/4 PLS-00201: identifier 'LV_PURCH_SALE' must be declared
16/4 PL/SQL: Statement ignored
17/4 PLS-00201: identifier 'LV_NAME_ADDRESS_ID' must be declared
17/4 PL/SQL: Statement ignored
19/4 PLS-00201: identifier 'LV_GET_NAA' must be declared
19/4 PL/SQL: Statement ignored
20/4 PLS-00201: identifier 'LV_FULL_NAME' must be declared
20/4 PL/SQL: Statement ignored
21/4 PLS-00201: identifier 'LV_PARENT_RECORD_ID' must be declared
21/4 PL/SQL: Statement ignored
22/4 PLS-00201: identifier 'LV_PASS' must be declared
22/4 PL/SQL: Statement ignored
27/6 PL/SQL: SQL Statement ignored
27/32 PLS-00201: identifier 'LV_GET_NAA' must be declared
34/6 PL/SQL: SQL Statement ignored
35/62 PL/SQL: ORA-00984: column not allowed here
40/6 PL/SQL: SQL Statement ignored
51/16 PL/SQL: ORA-00904: "LV_NAME_AND_ADDRESS_ID": invalid identifier
Errors: check compiler log
Any assistance would be appreciated.