Hi All,
I have XXC_MAP as a parent table, XXC_ORDER_DET is a child table.
I will need to update XXC_ORDER_DET columns with XXC_MAP columns.
what is the best way.. i was facing an issue while updating using BULK COLLECT.
DECLARE
CURSOR c_item_coa IS
SELECT ap_coa.account,
ap_coa.office_acct,
ap_coa.line_acct,
ap_coa.exp_acct,
ap_coa.loc_acct
FROM xxc_map ap_coa,
xxc_order_det aiv
WHERE 1 = 1
AND aiv.order_acct = ap_coa.account;
----
TYPE c_coa_rec
IS TABLE OF c_item_coa%ROWTYPE INDEX BY PLS_INTEGER;
lv_c_coa_rec C_COA_REC;
BEGIN
BEGIN
OPEN c_item_coa;
LOOP
FETCH c_item_coa bulk collect INTO lv_c_coa_rec limit 10000;
forall i IN 1 .. lv_c_coa_rec.count
------
UPDATE xxc_order_det
SET det_office = Lv_c_coa_rec(i).office_acct,
det_line = Lv_c_coa_rec(i).line_acct,
det_exp = Lv_c_coa_rec(i).exp_acct,
det_loc = Lv_c_coa_rec(i).loc_acct
WHERE order_acct = Lv_c_coa_rec(i).account;
------
EXIT WHEN c_item_coa%NOTFOUND;
END LOOP;
CLOSE c_item_coa;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('Unexpected error occured while updating '
|| SQLCODE
|| '-'
|| SQLERRM);
END;
/
Thanks