Home » SQL & PL/SQL » SQL & PL/SQL » using forall and bulk collect together (Oracle 9.0.0.2)
using forall and bulk collect together [message #343666] |
Wed, 27 August 2008 15:20  |
thalladas
Messages: 16 Registered: August 2008
|
Junior Member |
|
|
Hey group,
I am trying to use bulk collect and forall together.
i have bulk collect on 3 columns and insert is on more than 3 columns.can anybody tell me how to reference those collection objects in bulk collect statement.
you can see the procedure,i highlighted things am trying.
Please let me know,if am clear enough.
PROCEDURE do_insert
IS
--
PROCEDURE process_insert_record
IS
CURSOR c_es_div_split
IS
SELECT div_id
FROM zrep_mpg_div
WHERE div_id IN ('PC', 'BP', 'BI', 'CI', 'CR');
--
PROCEDURE write_record
IS
CURSOR c_plan_fields
IS
SELECT x.comp_plan_id, x.comp_plan_cd, cp.comp_plan_nm
FROM cp_div_xref@dm x, comp_plan@dm cp
WHERE x.comp_plan_id = cp.comp_plan_id
AND x.div = v_div
AND x.sorg_cd = v_sorg_cd
AND x.comp_plan_yr = TO_NUMBER (TO_CHAR (v_to_dt, 'yyyy'));
TYPE test1 IS TABLE OF c_plan_fields%ROWTYPE
INDEX BY BINARY_INTEGER;
test2 test1;
--
BEGIN -- write_record
OPEN c_plan_fields;
FETCH c_plan_fields bulk collect INTO test2;
CLOSE c_plan_fields;
ForAll X In 1..test2.last
INSERT INTO cust_hier
(sorg_cd, cust_cd, bunt, --DP
div,
from_dt,
to_dt,
cust_ter_cd, cust_rgn_cd, cust_grp_cd,
cust_area_cd, sorg_desc, cust_nm, cust_ter_desc,
cust_rgn_desc, cust_grp_desc, cust_area_desc,
cust_mkt_cd, cust_mkt_desc, curr_flag,
last_mth_flag, comp_plan_id, comp_plan_cd,
comp_plan_nm, asgn_typ, lddt
)
VALUES (v_sorg_cd, v_cust_cd, v_bunt, --DP
v_div,
TRUNC (v_from_dt),
TO_DATE (TO_CHAR (v_to_dt, 'mmddyyyy') || '235959',
'mmddyyyyhh24miss'
),
v_ter, v_rgn, v_grp,
v_area, v_sorg_desc, v_cust_nm, v_cust_ter_desc,
v_rgn_desc, v_grp_desc, v_area_desc,
v_mkt, v_mkt_desc, v_curr_flag,
v_last_mth_flag, test2(x).comp_plan_id,test2(x).comp_plan_cd,
test2(x).comp_plan_nm, v_asgn_typ, v_begin_dt
);
v_plan_id := 0;
v_plan_cd := 0;
v_plan_nm := NULL;
v_out_cnt := v_out_cnt + 1;
IF doing_both
THEN
COMMIT;
ELSE
-- commiting v_commit_rows rows at a time.
IF v_out_cnt >= v_commit_cnt
THEN
COMMIT;
p.l ( 'Commit point reached: '
|| v_out_cnt
|| 'at: '
|| TO_CHAR (SYSDATE, 'mm/dd hh24:mi:ss')
);
v_commit_cnt := v_commit_cnt + v_commit_rows;
END IF;
END IF;
END write_record;
|
|
|
|
|
|
|
Re: using forall and bulk collect together [message #343684 is a reply to message #343675] |
Wed, 27 August 2008 16:46   |
thalladas
Messages: 16 Registered: August 2008
|
Junior Member |
|
|
Here is formmated code:
sorry for the inconveninence,
PROCEDURE do_insert
IS
--
PROCEDURE process_insert_record
IS
CURSOR c_es_div_split
IS
SELECT div_id
FROM zrep_mpg_div
WHERE div_id IN ('PC', 'BP', 'BI', 'CI', 'CR');
--
PROCEDURE write_record
IS
CURSOR c_plan_fields
IS
SELECT x.comp_plan_id, x.comp_plan_cd, cp.comp_plan_nm
FROM cp_div_xref@dm x, comp_plan@dm cp
WHERE x.comp_plan_id = cp.comp_plan_id
AND x.div = v_div
AND x.sorg_cd = v_sorg_cd
AND x.comp_plan_yr = TO_NUMBER (TO_CHAR (v_to_dt, 'yyyy'));
[B]TYPE test1 IS TABLE OF c_plan_fields%ROWTYPE
INDEX BY BINARY_INTEGER;[/B] test2 test1;
--
BEGIN -- write_record
OPEN c_plan_fields;
[B] FETCH c_plan_fields bulk collect INTO test2;[/B]
CLOSE c_plan_fields;
[B] ForAll X In 1..test2.last[/B]
INSERT INTO cust_hier
(sorg_cd, cust_cd, bunt, --DP
div,from_dt,to_dt,cust_ter_cd, cust_rgn_cd, cust_grp_cd,
cust_area_cd, sorg_desc, cust_nm, cust_ter_desc,
cust_rgn_desc, cust_grp_desc, cust_area_desc,
cust_mkt_cd, cust_mkt_desc, curr_flag,
last_mth_flag, comp_plan_id, comp_plan_cd,
comp_plan_nm, asgn_typ, lddt
)
VALUES
(v_sorg_cd, v_cust_cd, v_bunt, --DP
v_div,TRUNC (v_from_dt),
TO_DATE (TO_CHAR(v_to_dt, 'mmddyyyy')|| '235959','mmddyyyyhh24miss'),
v_ter, v_rgn, v_grp,v_area, v_sorg_desc, v_cust_nm,v_cust_ter_desc,v_rgn_desc,
v_grp_desc, v_area_desc,v_mkt, v_mkt_desc, v_curr_flag,
v_last_mth_flag,[B] test2(x).comp_plan_id,test2(x).comp_plan_cd,
test2(x).comp_plan_nm,[/B] v_asgn_typ, v_begin_dt
);
v_plan_id := 0;
v_plan_cd := 0;
v_plan_nm := NULL;
v_out_cnt := v_out_c[CODE][/CODE]nt + 1;
IF doing_both
THEN
COMMIT;
ELSE
-- commiting v_commit_rows rows at a time.
IF v_out_cnt >= v_commit_cnt
THEN
COMMIT;
p.l ( 'Commit point reached: '|| v_out_cnt
|| 'at: '|| TO_CHAR (SYSDATE, 'mm/dd hh24:mi:ss'));
v_commit_cnt := v_commit_cnt + v_commit_rows;
END IF;
END IF;
END write_record;
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 16:40:04 CST 2025
|