'How to fetch the records from cursor which contains columns from different table?
FIRST PROCEDURE
create or replace PROCEDURE TESTPROCEDURE ( P_LAF_PK IN NUMBER, P_RET_VAL OUT SYS_REFCURSOR ) AS BEGIN OPEN p_RET_VAL FOR
SELECT LAF.AF_FEE,LAF.AF_FEES_PAYABLE, LAH.LH_DECISION_DT,LAH.LH_ISSUED FROM LH_APP_HDR LAH JOIN LIQ_APP_FEE LAF ON AF_PK = LH_PK WHERE LAF_APPID = P_LAF_PK;
END TESTPROCEDURE;
Calling this procedure in another procedure
SECOND PROCEDURE
create or replace PROCEDURE TESTPROCEDURE1 AS BEGIN
DECLARE
V_LIQ_CURSOR SYS_REFCURSOR;
V_LIQ_CURSOR_OUT1 LIQ_APP_FEE%ROWTYPE;
BEGIN
TESTPROCEDURE (2727,V_LIQ_CURSOR);
LOOP
FETCH V_LIQ_CURSOR INTO V_LIQ_CURSOR_OUT1; -- getting error in this line like "Return types of Result Set variables or query do not match"
EXIT WHEN V_LIQ_CURSOR%NOTFOUND;
IF(V_LIQ_CURSOR_OUT1.AF_FEE != 0) THEN
SELECT (V_LIQ_CURSOR_OUT1.AF_FEES_PAYABLE + V_LIQ_CURSOR_OUT1.AF_FEE) INTO V_TOTALFEE FROM DUAL;
END IF;
END TESTPROCEDURE1;
Can anyone tell me how to get the result from cursor which contains multiple table columns.
NOTE:
I want only two columns from the first procedure
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
