I wrote a procedure where i am trying to insert value from source to destination table, i used bulk collect in order to execute large volume of data.
While executing the below procedure, its showing cursor cus2 declaration is incomplete and its showing some column ambiguously defined in the cursor.
create or replace PROCEDURE TEST2 (
p_array_size IN NUMBER
) IS
CURSOR cur1 IS SELECT DISTINCT
*
FROM
test
CURSOR cur3 IS SELECT * FROM test;
CURSOR cur2(BND_TYPE cr_loan_prima_rate_orig.bndng_typ%TYPE, BND_VAL cr_loan_prima_rate_orig.bndng_val%TYPE, FINANCIAL_INST_ID cr_loan_prima_rate_orig.financial_institution_id%TYPE,
PRDCT_ID cr_loan_prima_rate_orig.prdct_id%TYPE,PRDCT_SUB_ID cr_loan_prima_rate_orig.prdct_sub_id%TYPE, INSTRUMENT_ID cr_loan_prima_rate_orig.instrmnt_id%TYPE) IS SELECT
d.*
FROM
test1 d,
(
SELECT
b.prdct_id,
FROM
test2 a,
test1 b
WHERE
a.ir_id = b.ir_id
AND a.price_component_id = b.price_component_id
AND a.financial_institution_id = b.financial_institution_id
GROUP BY
b.prdct_id,
) e
WHERE
d.prdct_id = e.prdct_id
AND d.bndng_typ = BND_TYPE
AND d.bndng_val = BND_VAL
AND d.financial_institution_id = FINANCIAL_INST_ID
AND d.prdct_id = PRDCT_ID
AND d.prdct_sub_id = PRDCT_SUB_ID
AND d.instrmnt_id = INSTRUMENT_ID ;
TYPE loan_data_tbl IS TABLE OF cur1%rowtype INDEX BY PLS_INTEGER;
loan_data loan_data_tbl;
TYPE loanrate_tbl IS TABLE OF cur2%rowtype INDEX BY BINARY_INTEGER;
loan_rate loanrate_tbl;
BEGIN
DECLARE
v_noofDays NUMBER:=0;
currentDt DATE;
BEGIN
SELECT * INTO currentDt FROM dt;
BEGIN
IF cur1%Isopen Then
Close cur1;
End IF;
IF cur2%Isopen Then
Close cur2;
End IF;
OPEN cur1;
LOOP
FETCH cur1 BULK COLLECT INTO loan_data LIMIT p_array_size;
EXIT WHEN loan_data.COUNT = 0;
FOR i IN 1..loan_data.COUNT
LOOP
OPEN cur3;
OPEN cur2(loan_data(i).bndng_typ, loan_data(i).bndng_val,loan_data(i).financial_institution_id,
loan_data(i).prdct_id, loan_data(i).prdct_sub_id, loan_data(i).instrmnt_id);
loop
FETCH cur2 BULK COLLECT INTO loan_rate LIMIT p_array_size;
EXIT WHEN loan_rate.COUNT = 0;
FOR j IN 1..loan_rate.COUNT
LOOP
IF(cur3.POS_NUM = loan_data(i).POS_NUM AND cur3.POS_TYPE = loan_data(i).POS_TYPE
AND cur3.PRICE_COMPONENT_ID = loan_rate(j).PRICE_COMPONENT_ID
AND cur3.RPRTD_TILL_DT = loan_data(i).RPRTD_TILL_DT) THEN
update test SET SEQ_NUM=1,
WHERE SEQ_NUM=2;
ELSE
INSERT INTO test VALUES (
....
....
);
END IF;
COMMIT;
END LOOP;
END LOOP;
CLOSE cur2;
CLOSE cur1;
END LOOP;
END LOOP;
CLOSE cur1;
END;
END;
End ;
/
For me everything looks good, not able to find the exact mistake.can anyone help me out with this.
