Home » SQL & PL/SQL » SQL & PL/SQL » Using Bulkinsert (Oracle 9.0.0.2)
Using Bulkinsert [message #342235] Thu, 21 August 2008 14:32 Go to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Hello Group,

I have a procedure which is taking for time for inserts.There are only primary indexes on the tables.when i dig into it,i found out its inserting row by row.I want to use Bulk collect and forall to make it run faster,but I unable use figure how to modify the current procedure..please guide me regarding this..

CREATE OR REPLACE PROCEDURE DW.load_xyz
AS
v_in_cnt NUMBER;
v_out_cnt NUMBER;
v_tot_in NUMBER := 0;
v_tot_out NUMBER := 0;
v_updt_cnt NUMBER;
v_dup_cnt NUMBER;
v_commit_rows NUMBER := 10000;
v_commit_cnt NUMBER := 10000;
v_begin_dt DATE;
v_end_dt DATE;
v_last_mth_dt DATE; --last bus month END DATE
v_last_day_dt DATE; --last bus day END DATE
first_rec BOOLEAN := TRUE;
doing_both BOOLEAN := FALSE;
mpg_only BOOLEAN := FALSE;
v_next_row NUMBER;
last_targ NUMBER;
last_rec BOOLEAN;
no_targ_found BOOLEAN;
write_dup BOOLEAN;
v_orig_from_dt DATE;
v_orig_to_dt DATE;
v_from_yr NUMBER (4);
v_to_yr NUMBER (4);
v_yr_diff NUMBER (4);

-- Variables for cust_hier fields
v_sorg_cd CUST_HIER.sorg_cd%TYPE;
v_cust_cd CUST_HIER.cust_cd%TYPE;
v_bunt CUST_HIER.bunt%TYPE; --DP
v_div CUST_HIER.div%TYPE;
v_from_dt CUST_HIER.from_dt%TYPE; -- source from_dt
v_to_dt CUST_HIER.to_dt%TYPE; -- source to_dt
v_from_dt_orig CUST_HIER.from_dt%TYPE; -- original from_dt
v_to_dt_orig CUST_HIER.to_dt%TYPE; -- original to_dt
t_from_dt CUST_HIER.from_dt%TYPE; -- target from_dt
t_to_dt CUST_HIER.to_dt%TYPE; -- target to_dt
v_ter CUST_HIER.cust_ter_cd%TYPE;
v_rgn CUST_HIER.cust_rgn_cd%TYPE;
v_grp CUST_HIER.cust_grp_cd%TYPE;
v_area CUST_HIER.cust_area_cd%TYPE;
v_mkt CUST_HIER.cust_mkt_cd%TYPE;
v_sorg_desc CUST_HIER.sorg_desc%TYPE;
v_cust_nm CUST_HIER.CUST_NM%TYPE;
v_cust_ter_desc CUST_HIER.cust_ter_desc%TYPE;
v_rgn_desc CUST_HIER.cust_rgn_desc%TYPE;
v_grp_desc CUST_HIER.cust_grp_desc%TYPE;
v_area_desc CUST_HIER.cust_area_desc%TYPE;
v_mkt_desc CUST_HIER.cust_mkt_desc%TYPE;
v_curr_flag CUST_HIER.curr_flag%TYPE;
v_last_mth_flag CUST_HIER.last_mth_flag%TYPE;
v_asgn_typ CUST_HIER.asgn_typ%TYPE;
v_plan_cd CUST_HIER.comp_plan_cd%TYPE;
v_plan_nm CUST_HIER.comp_plan_nm%TYPE;
v_plan_id CUST_HIER.comp_plan_id%TYPE;
v_mpg MPG_DIV_S004.mpg_id%TYPE;
v_src_values VARCHAR2 (2000);
v_prev_values VARCHAR2 (2000);

/* Error handling variables */
v_error_code NUMBER;
v_error_message VARCHAR2 (200);

/* CURSORS */
/*
** Source records - CUSTs with only ONE of DIV or MPG type assignment
** Used in sequential logic, no reading of committed records.
*/
CURSOR c_mpg
IS
SELECT/*+ ALL_ROWS */
DISTINCT sorg_cd, mpg, bunt, --DP
bunt_orig, -- ES/CS 5/2002
div, cust_cd, to_dt, from_dt,
cust_rgn_cd, cust_grp_cd, cust_area_cd, CUST_NM,
cust_rgn_desc, cust_grp_desc, cust_area_desc, sorg_desc,
cust_ter_cd, cust_ter_desc, mkt_cd, mkt_desc,
'mpg' asgn_typ, dflt_flag
FROM CUST_HIER_TAB z
WHERE sorg_cd IN ('S004')
AND z.mpg IN ('01', '02', '05', '06')
--AND z.cust_cd = '27174'
ORDER BY z.sorg_cd,
z.mpg,
z.cust_cd,
z.to_dt DESC,
z.dflt_flag DESC,
z.from_dt DESC;

-- changed cursor this record is based on from c_mpg_only to c_mpg 5/2002 CJL
src_rec c_mpg%ROWTYPE;

/*
** Source records - CUSTs with mpg assignments only assign for AB,AF,PC,RD
** Used in sequential logic, no reading of committed records.
** Added NV and VS, CS 5/2002.
** Added AX 12/2005
** Added VC 8/2006
*/
CURSOR c_div_only_mpg
IS
SELECT/*+ ALL_ROWS */
DISTINCT z.sorg_cd, z.mpg, z.bunt, -- DP
z.bunt_orig, -- ES/CS 5/2002
NVL (zd.div_id, 'na') div,
cust_cd, to_dt, from_dt, cust_rgn_cd, cust_grp_cd,
cust_area_cd, CUST_NM, cust_rgn_desc, cust_grp_desc,
cust_area_desc, sorg_desc, cust_ter_cd, cust_ter_desc,
mkt_cd, mkt_desc,
DECODE (z.div, 'na', 'mpg', 'div') asgn_typ,
dflt_flag
FROM CUST_HIER_TAB z, ZREP_MPG_DIV zd
WHERE sorg_cd IN ('S004','S096')
AND ( z.mpg = zd.mpg_id
OR z.div = zd.div_id)
-- Added NV, VS, CS 5/2002
-- Added AX 12/2005
-- Added VC 8/2006
AND zd.div_id IN ('AB', 'AF', 'PC', 'RD', 'NV', 'VS', 'CS', 'AX','VC')
AND NOT EXISTS (SELECT 1
FROM CUST_HIER_TAB c2
WHERE c2.cust_cd = z.cust_cd
AND c2.div = zd.div_id)
ORDER BY z.sorg_cd,
z.cust_cd,
z.bunt_orig, -- ES/CS 5/2002
div,
asgn_typ,
z.to_dt DESC,
z.dflt_flag DESC,
z.from_dt DESC;

/*
** Source records - CUSTs with both div and mpg assignments
** Used in process_new_record logic where target is read, not sequential
** 5/2002 CJL - added field bunt_orig to source tab for use in order by.
** This was necessary to allow ES PC/NV customer-territories to be written
** before VI. PC/NV team splits required division level allignments. From
** a customer not srep perspective ES territories need to take priority.
*/
CURSOR c_mpg_and_div
IS
SELECT/*+ ALL_ROWS */
DISTINCT z.sorg_cd, z.mpg, z.bunt, --DP
z.bunt_orig, -- ES/CS 5/2002
NVL (zd.div_id, 'na') div,
cust_cd, to_dt, from_dt, cust_rgn_cd, cust_grp_cd,
cust_area_cd, CUST_NM, cust_rgn_desc, cust_grp_desc,
cust_area_desc, sorg_desc, cust_ter_cd, cust_ter_desc,
mkt_cd, mkt_desc,
DECODE (z.div, 'na', 'mpg', 'div') asgn_typ, dflt_flag
FROM CUST_HIER_TAB z, ZREP_MPG_DIV zd
WHERE sorg_cd IN ('S004','S096')
AND ( z.mpg = zd.mpg_id
OR z.div = zd.div_id)
-- Added NV, VS, CS 5/2002
-- Added AX 12/2005
-- Added VC 8/2006
AND zd.div_id IN ('AB', 'AF', 'PC', 'RD', 'NV', 'VS', 'CS', 'AX','VC')
AND EXISTS (SELECT 1
FROM CUST_HIER_TAB c2
WHERE c2.cust_cd = z.cust_cd
AND c2.div = zd.div_id)
ORDER BY z.sorg_cd,
z.cust_cd,
z.bunt_orig, -- ES/CS 5/2002
div,
asgn_typ,
z.to_dt DESC,
z.dflt_flag DESC,
z.from_dt DESC;

--
/*
** Set of divisions to expand to for an MPG
** mpg_div_s004 is unique to this process in the US datamart.
** Only the divisions that are NOT used in a division level allignment
** are included in mpg_div_s004.
*/
CURSOR c_mpg_div
IS
SELECT div_id
FROM MPG_DIV_S004
WHERE mpg_id = v_mpg
ORDER BY div_id;

--
CURSOR c_targ_dates (
p_sorg VARCHAR2,
p_cust VARCHAR2,
p_div VARCHAR2,
p_bunt VARCHAR2
) --DP
IS
-- cust_hier_key table replaced by view 5/2002
SELECT/*+ INDEX (cust_hier_pk) */
from_dt, to_dt
FROM CUST_HIER
WHERE sorg_cd = p_sorg
AND bunt = p_bunt --DP
AND div = p_div
AND cust_cd = p_cust
ORDER BY to_dt DESC;

TYPE t_targ_dates IS TABLE OF c_targ_dates%ROWTYPE
INDEX BY BINARY_INTEGER;

targ_tab t_targ_dates;

--
--
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'));

--
BEGIN -- write_record
OPEN c_plan_fields;
FETCH c_plan_fields INTO v_plan_id, v_plan_cd, v_plan_nm;
CLOSE c_plan_fields;


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,
v_plan_id,
v_plan_cd,
v_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;

FUNCTION write_div
RETURN BOOLEAN
IS
return_true_false BOOLEAN;
BEGIN

IF v_to_dt < TO_DATE ('08012001', 'mmddyyyy')
AND ( v_div = 'BH'
OR v_div = 'TH')
THEN
-- Start of BH/TH at CRM
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ( '10012001', 'mmddyyyy')
AND v_div = 'RD'
THEN
-- Start of RD at USA/VI
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ( '01012002', 'mmddyyyy')
AND ( v_div = 'DD'
OR v_div = 'CK')
THEN
-- Start of DD/CK at VI
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ('12012001', 'mmddyyyy')
AND v_div = 'NV'
THEN
-- Start of NV at ES
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ('01012002', 'mmddyyyy')
AND v_div = 'LP'
THEN
-- Start of LP at ES
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ('01012003', 'mmddyyyy')
AND ( v_div = 'AN'
OR v_div = 'AX'
OR v_div = 'BT'
OR v_div = 'FB'
OR v_div = 'VH')
THEN
-- Start of AN,AX,BT,FB,VH at CS
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ('01012005', 'mmddyyyy')
AND ( v_div = 'CR'
OR v_div = 'CI'
OR v_div = 'BP'
OR v_div = 'BI'
OR v_div = 'PM')
THEN
-- Start of CR,CI,BP,BI at ES and PM at CRM
return_true_false := FALSE;
ELSIF v_to_dt < TO_DATE ('01012005', 'mmddyyyy')
AND v_div = 'VC'
THEN
-- Start of legacy VC at VI/ABT
return_true_false := FALSE;
ELSE
return_true_false := TRUE;
END IF;

RETURN return_true_false;
END write_div;
--
--
BEGIN -- process_insert_record
--p.l('Start process_insert_record');
IF v_last_day_dt BETWEEN v_from_dt AND v_to_dt
THEN
v_curr_flag := 1;
ELSE
v_curr_flag := 0;
END IF;

IF v_last_mth_dt BETWEEN v_from_dt AND v_to_dt
THEN
v_last_mth_flag := 1;
ELSE
v_last_mth_flag := 0;
END IF;

IF mpg_only
THEN
-- for each division to expand to
--p.l('Loop Bunt/MPG: ' || v_bunt || '/' || v_mpg);
FOR v_mpg_div IN c_mpg_div
LOOP
v_div := v_mpg_div.div_id;

--p.l('Div in loop: ' || v_div);
IF write_div
THEN
write_record;
END IF;
END LOOP;
--p.l('End Loop Bunt/MPG');
--p.l(' ');
ELSE
--p.l(' No Loop Bunt/MPG/div: '||v_bunt||'/'||v_mpg||'/'||v_div);
IF v_div = 'PC'
-- ES/05 split of PC division into 4 new divisions.
THEN
FOR cdiv IN c_es_div_split
LOOP
v_div := cdiv.div_id;
IF write_div
THEN
write_record;
END IF;
END LOOP;
v_div := 'PC';
ELSE
IF write_div
THEN
write_record;
END IF;
END IF;
END IF;
--p.l('End process_insert_record');
END process_insert_record;
--
--
BEGIN -- do_insert
--p.l('Sorg: ' || v_sorg_cd|| ' Cust: ' || v_cust_cd );
--p.l(' DIV: ' || v_div||'from_dt: '||v_from_dt||' to_dt: '||v_to_dt);
v_orig_from_dt := v_from_dt;
v_orig_to_dt := v_to_dt;
v_from_yr := TO_NUMBER (TO_CHAR (v_from_dt, 'yyyy'));
v_to_yr := TO_NUMBER (TO_CHAR (v_to_dt, 'yyyy'));
v_yr_diff := v_to_yr - v_from_yr;

IF v_yr_diff = 0
THEN
process_insert_record;
END IF;

IF v_yr_diff > 0
THEN -- write first year record
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr), 'mmddyyyy');
process_insert_record;

IF v_yr_diff = 1
THEN -- write 2nd year record
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
ELSIF v_yr_diff = 2
THEN -- write 2nd and 3rd records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
ELSIF v_yr_diff = 3
THEN -- write 2nd, 3rd and 4th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
ELSIF v_yr_diff = 4
THEN -- write 2nd, 3rd, 4th and 5th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
ELSIF v_yr_diff = 5
THEN -- write 2nd, 3rd, 4th 5th and 6th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
ELSIF v_yr_diff = 6
THEN -- write 2nd, 3rd, 4th 5th 6th 7th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
------
ELSIF v_yr_diff = 7
THEN -- write 2nd, 3rd, 4th 5th 6th 7th 8th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
-- v_to_dt := v_orig_to_dt; -- Todd: this is wrong, I commented it out.
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 7), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
------
ELSIF v_yr_diff = 8
THEN -- write 2nd, 3rd, 4th 5th 6th 7th 8th 9th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 7), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 7), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + Cool, 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
------
ELSIF v_yr_diff = 9
THEN -- write 2nd, 3rd, 4th 5th 6th 7th 8th 9th 10th records
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 6), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 7), 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 7), 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + Cool, 'mmddyyyy');
v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + Cool, 'mmddyyyy');
process_insert_record;
v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 9), 'mmddyyyy');
v_to_dt := v_orig_to_dt;
process_insert_record;
------
ELSE -- abend with error
p.l ('End year: ' || v_to_yr || ' is greater than 2007');
RAISE_APPLICATION_ERROR (-20001, 'End Year greater than 2007');
END IF;
END IF;

v_from_dt := v_orig_from_dt;
v_to_dt := v_orig_to_dt;


END do_insert;

--
--
PROCEDURE save_source -- save source in global variables
IS
BEGIN
v_sorg_cd := src_rec.sorg_cd;
v_cust_cd := src_rec.cust_cd;
v_mpg := src_rec.mpg;
v_bunt := src_rec.bunt; --DP
v_div := src_rec.div;
v_from_dt := src_rec.from_dt;
v_to_dt := src_rec.to_dt;
v_from_dt_orig := src_rec.from_dt;
v_to_dt_orig := src_rec.to_dt;
v_ter := src_rec.cust_ter_cd;
v_rgn := src_rec.cust_rgn_cd;
v_grp := src_rec.cust_grp_cd;
v_area := src_rec.cust_area_cd;
v_sorg_desc := src_rec.sorg_desc;
v_cust_nm := src_rec.CUST_NM;
v_cust_ter_desc := src_rec.cust_ter_desc;
v_rgn_desc := src_rec.cust_rgn_desc;
v_grp_desc := src_rec.cust_grp_desc;
v_area_desc := src_rec.cust_area_desc;
v_mkt := src_rec.mkt_cd;
v_mkt_desc := src_rec.mkt_desc;
v_asgn_typ := src_rec.asgn_typ;
v_prev_values := v_ter || v_rgn || v_grp || v_area;
END save_source;

--
--
PROCEDURE clear_keys -- clear key fields
IS
BEGIN
v_sorg_cd := NULL;
v_cust_cd := NULL;
v_mpg := NULL;
v_bunt := NULL;
v_div := NULL;
v_from_dt := NULL;
v_to_dt := NULL;
v_from_dt_orig := NULL;
v_to_dt_orig := NULL;
END clear_keys;

--
--
PROCEDURE fill_targ_tab (
sorg_in IN CUST_HIER.sorg_cd%TYPE,
cust_in IN CUST_HIER.cust_cd%TYPE,
div_in IN CUST_HIER.div%TYPE,
bunt_in IN CUST_HIER.bunt%TYPE --DP
)
IS
BEGIN
targ_tab.DELETE;
no_targ_found := TRUE;
last_targ := 0;

FOR targ_dates_rec IN c_targ_dates (
sorg_in,
cust_in,
div_in,
bunt_in
) --DP
LOOP
--p.l('Found a targ record');
no_targ_found := FALSE;
v_next_row := NVL (targ_tab.LAST, 0) + 1;
targ_tab (v_next_row) := targ_dates_rec;
END LOOP;

last_targ := targ_tab.LAST;
END fill_targ_tab;

--
--
PROCEDURE process_new_record
IS
BEGIN
/*
** Put all existing target records for given sorg,
** cust and div in a PL/SQL table
*/
fill_targ_tab (v_sorg_cd, v_cust_cd, v_div, v_bunt); --DP

IF no_targ_found
THEN
--p.l ('1-no_targ_found after looking for ' || v_div);
do_insert;
ELSE -- possibly add but do not over-ride
last_rec := FALSE;

FOR i IN 1 .. last_targ
LOOP
IF i = last_targ
THEN
--p.l ('2-This is the last targ rec in pl/sql tab');
last_rec := TRUE; -- just for readability later
END IF;

t_from_dt := targ_tab (i).from_dt;
t_to_dt := targ_tab (i).to_dt;

--p.l ('3-Executed tab loop ' || i || ' times.');
--p.l ('3-targ rec start: ' || t_from_dt || 'targ rec end : ' || t_to_dt);

IF v_to_dt > t_to_dt
THEN
--p.l ('4-when src_end is after targ_end = always an insert' );
IF v_from_dt > t_to_dt
THEN
--p.l ('5-when src_st after targ_end, insert');
do_insert;
EXIT;
ELSIF v_from_dt >= t_from_dt
THEN
--p.l ('6-when s_st is at or after targ_st, insert src');
--p.l ('6-after targ by setting the new s_st date');
v_from_dt := TRUNC (t_to_dt) + 1;
do_insert;
EXIT;
ELSE
--p.l ('7-when src_st is before targ_st, insert src after targ');
--p.l ( '7-then look to see if src can be added after targ' );
v_from_dt := TRUNC (t_to_dt) + 1;
do_insert;
--write_dup := FALSE;
v_to_dt := TRUNC (t_from_dt) - 1 / (24 * 60 * 60);
v_from_dt := v_from_dt_orig;

IF last_rec
THEN
--p.l ( '8-when this is last targ, insert after targ' );
do_insert;
-- naturally exits with last_rec
ELSE
--p.l ('9-when not last targ rec, get another one');
NULL;
END IF;
END IF;
ELSIF v_from_dt >= t_from_dt
THEN
--p.l ('10-s_from >= t_from and s_to < t_to == contained/dup');
--do_dup_insert;
EXIT;
ELSE
--p.l ('15-s_from < t_from and s_to <= t_to');
IF v_to_dt >= t_from_dt
THEN
--p.l ('16-src is before targ, adjust s_to');
v_to_dt := TRUNC (t_from_dt) - 1 / (24 * 60 * 60);

IF last_rec
THEN
--p.l ('19-when last targ rec, insert');
do_insert;
-- naturally exits with last_rec
ELSE
--p.l ('20-when more targ, get another, look for end');
NULL;
END IF;
ELSE
--p.l ('21-src_from and src_to < t_from');
IF last_rec
THEN
--p.l ('22-when last targ rec, insert');
do_insert;
-- naturally exits with last_rec
ELSE
--p.l ( '23-when more targ recs, targ could still fit' );
NULL;
END IF;
END IF;
END IF; -- starting if
END LOOP; -- targ_tab loop
END IF; -- no_dirs_found
END process_new_record;
--
--
BEGIN


-- Initialize meta data variables
v_begin_dt := SYSDATE;
v_in_cnt := 0;
v_out_cnt := 0;
/*
** Get dates for last end of busines day and last closed month
*/
SELECT d_val
INTO v_last_day_dt
FROM GP_PARMS
WHERE KEY = 'end_of_business'
AND parm = 'day';

SELECT d_val
INTO v_last_mth_dt
FROM GP_PARMS
WHERE KEY = 'end_of_business'
AND parm = 'month';

p.l (' ');
p.l ('End of business day is: ' || TO_CHAR (v_last_day_dt, 'mm/dd/yyyy'));
p.l ('End of business month is: ' || TO_CHAR (v_last_mth_dt, 'mm/dd/yyyy'));
p.l (' ');

-- DELETE RECORDS THAT ARE CURRENTLY IN THE TARGET TABLE
truncate_table ('cust_hier');
p.l ('Table cust_hier truncated.');

--
-- Create the default record
INSERT INTO CUST_HIER
(
sorg_cd,
bunt, --DP
div,
from_dt,
to_dt,
cust_cd,
cust_ter_cd,
cust_rgn_cd,
cust_grp_cd,
cust_area_cd,
CUST_NM,
cust_ter_desc,
cust_rgn_desc,
cust_grp_desc,
cust_area_desc,
sorg_desc,
cust_mkt_cd,
cust_mkt_desc,
comp_plan_id,
comp_plan_cd,
comp_plan_nm,
curr_flag,
last_mth_flag,
asgn_typ,
lddt
)
VALUES (
'S004',
'na', --DP
'na',
TO_DATE ('01011900', 'mmddyyyy'),
TO_DATE ('12319999235959', 'mmddyyyyhh24miss'),
'na',
'na',
'na',
'na',
'na',
'Not Known',
'Not Known',
'Not Known',
'Not Known',
'Not Known',
'US Sales Org',
'USA',
'United States',
0,
'na',
'Missing',
1,
1,
'div',
SYSDATE
);

COMMIT;
--
doing_both := TRUE;
mpg_only := FALSE;
p.l (
'Start CUSTs with both MPG and DIV assignments at: ' ||
TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
);
p.l (' ');
/*
** Process AB AF PC RD Divisions for customers with MPG and DIV assignments
** Added NV and VS, CS 5/2002.
** Added AX 12/2005
** Added VC 8/2006
*/
first_rec := TRUE;
clear_keys;
OPEN c_mpg_and_div;

LOOP
FETCH c_mpg_and_div INTO src_rec;
EXIT WHEN c_mpg_and_div%NOTFOUND;
v_in_cnt := v_in_cnt + 1;
--write_dup := TRUE;
--
save_source;
process_new_record;
END LOOP;

COMMIT;
CLOSE c_mpg_and_div;

p.l (
'Customers with both MPG and DIV assignments done: ' ||
TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
);
p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt);
v_tot_in := v_tot_in + v_in_cnt;
v_tot_out := v_tot_out + v_out_cnt;
v_in_cnt := 0;
v_out_cnt := 0;
p.l (
'Total Input records: ' || v_tot_in || ', Total Output records: ' ||
v_tot_out
);
p.l ('Input/Output ratio: ' || TO_CHAR (v_tot_in / v_tot_out, '999.99'));
p.l (' ');
--
--
/*
** Start of MPG only processing, sequential without read of target
*/
doing_both := FALSE;
mpg_only := FALSE;
p.l (' ');
p.l (
'Start sequencial loop processing at: ' ||
TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
);
--
/*
** Process AB AF PC RD Divisions for customers with only MPG assignments
** Added NV and VS, CS 5/2002.
** Added AX 12/2005
** Added VC 8/2006
*/
first_rec := TRUE;
clear_keys;
OPEN c_div_only_mpg;

LOOP
FETCH c_div_only_mpg INTO src_rec;

-- Process last record
IF c_div_only_mpg%NOTFOUND
AND NOT first_rec
THEN
do_insert;
END IF;

EXIT WHEN c_div_only_mpg%NOTFOUND;
v_in_cnt := v_in_cnt + 1;

IF src_rec.sorg_cd = v_sorg_cd --src_key = prev_key
AND src_rec.cust_cd = v_cust_cd
AND src_rec.bunt = v_bunt --DP
AND src_rec.div = v_div
THEN
--p.l ('S1-same key, looking for same hierarchy');
v_src_values :=
src_rec.cust_ter_cd || src_rec.cust_rgn_cd || src_rec.cust_grp_cd ||
src_rec.cust_area_cd;

IF v_src_values = v_prev_values
THEN
--p.l ( 'S2-same key and hierarchy' );
IF src_rec.from_dt <= v_from_dt
THEN
--p.l( 'S3-if src from_dt is earlier, use it' );
v_from_dt := src_rec.from_dt;
END IF;
ELSE
--p.l ( 'S4-- same key but new hierarchy, check date' );
--p.l ( 'S4 S_from_dt: ' || src_rec.from_dt ||
-- ' V_from_dt: ' || v_from_dt);
--insert only when src from_dt is earlier even if hier has changed
IF src_rec.from_dt < v_from_dt
THEN
--p.l ('S5-Insert previous and save source');
do_insert;
-- set source rec to_dt to be just prior to previous rec from_dt
-- this enforces contiguous allignments (no time overlaps)
src_rec.to_dt := TRUNC (v_from_dt) - 1 / (24 * 60 * 60);
save_source;
END IF;
END IF;
ELSE
--p.l ( 'S6- New Key' );
IF first_rec
THEN
first_rec := FALSE;
ELSE
--p.l( 'S7- Inserting previous record' );
do_insert;
END IF;

save_source;
END IF; -- src_key = prev_key
END LOOP; -- rec in c_div_only_mpg cursor loop
COMMIT;
CLOSE c_div_only_mpg;

p.l (
'Division assign. Customers with only MPG assign. done: ' ||
TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
);
p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt);
v_tot_in := v_tot_in + v_in_cnt;
v_tot_out := v_tot_out + v_out_cnt;
v_in_cnt := 0;
v_out_cnt := 0;
p.l (
'Total Input records: ' || v_tot_in || ', Total Output records: ' ||
v_tot_out
);
p.l ('Input/Output ratio: ' || TO_CHAR (v_tot_in / v_tot_out, '999.99'));
p.l (' ');
--
--
/*
*******************************
** Process pure MPG assignments
*******************************
*/
doing_both := FALSE;
mpg_only := TRUE;
first_rec := TRUE;
clear_keys;
OPEN c_mpg;

LOOP
FETCH c_mpg INTO src_rec;

-- Process last record
IF c_mpg%NOTFOUND
AND NOT first_rec
THEN
do_insert;
END IF;

EXIT WHEN c_mpg%NOTFOUND;
v_in_cnt := v_in_cnt + 1;

/*
** Troubleshooting
**
p.l('New record');
p.l('V sorg/cust/bunt: ' || v_sorg_cd || v_cust_cd || v_bunt);
p.l('S sorg/cust/bunt: ' || src_rec.sorg_cd || src_rec.cust_cd ||
src_rec.bunt);
p.l(' ');
**
*/

IF src_rec.sorg_cd = v_sorg_cd --src_key = prev_key
AND src_rec.cust_cd = v_cust_cd
AND src_rec.mpg = v_mpg
THEN
--p.l ('MS1-same key, looking for same hierarchy');
v_src_values :=
src_rec.cust_ter_cd || src_rec.cust_rgn_cd || src_rec.cust_grp_cd ||
src_rec.cust_area_cd;

IF v_src_values = v_prev_values
THEN
--p.l ( 'MS2-same key and hierarchy' );
IF src_rec.from_dt <= v_from_dt
THEN
--p.l( 'MS3-if src from_dt is earlier, use it' );
v_from_dt := src_rec.from_dt;
END IF;
ELSE
--p.l ( 'MS4-- same key but new hierarchy, check date' );
--p.l ( 'MS4 S_from_dt: ' || src_rec.from_dt ||
-- ' V_from_dt: ' || v_from_dt);
--insert only when src from_dt is earlier even if hier has changed
IF src_rec.from_dt < v_from_dt
THEN
--p.l ('MS5-Insert previous and save source');
do_insert;
-- set source rec to_dt to be just prior to previous rec from_dt
-- this enforces contiguous allignments (no time overlaps)
src_rec.to_dt := TRUNC (v_from_dt) - 1 / (24 * 60 * 60);
save_source;
END IF;
END IF;
ELSE
--p.l ( 'MS6- New Key' );
IF first_rec
THEN
first_rec := FALSE;
--p.l('First record in c_mpg cursor detected');
ELSE
--p.l( 'MS7- Inserting previous record' );
do_insert;
END IF;

save_source;
END IF; -- src_key = prev_key
END LOOP; -- rec in c_mpg cursor loop

COMMIT;
CLOSE c_mpg;


mpg_only := FALSE;
p.l (
'Customers with only MPG assignments done: ' ||
TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
);
p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt);
p.l (' ');
v_tot_in := v_tot_in + v_in_cnt;
v_tot_out := v_tot_out + v_out_cnt;
p.l (
'Total Input records: ' || v_tot_in || ', Total Output records: ' ||
v_tot_out
);
--
--
p.l ('Analyze tables started: '||TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss'));
atab ('cust_hier');
--
--
v_end_dt := SYSDATE;
p.l (' ');
p.l ('Finished at: ' || TO_CHAR (v_end_dt, 'mm/dd/yyyy hh24:mi:ss'));
--

--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_code := SQLCODE;
v_error_message := SUBSTR (SQLERRM, 1, 200);
p.l ('Failure during end of business date processing');
p.l ('ERROR : ' || v_error_message);
raise_application_error(-20001, v_error_message);
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_message := SUBSTR (SQLERRM, 1, 200);
p.l ('Error: ' || v_error_message);
p.l ('Sorg: ' || v_sorg_cd || ' CUST: ' || v_cust_cd);
p.l ( ' DIV: '||v_div||' BUNT: '||v_bunt||' from_dt: '||v_from_dt||
' to_dt: ' || v_to_dt); --DP
p.l ( 'v_from: '||v_from_dt||' v_to: '||v_to_dt||' v_pcd: '|| v_plan_cd);
p.l ( 'o_from: ' || v_orig_from_dt || ' o_to: ' || v_orig_to_dt ||
' fyr: ' || v_from_yr || ' tyr: ' || v_to_yr ||
' yr_dif: ' || v_yr_diff);
raise_application_error(-20001, v_error_message);
END;
/

Re: Using Bulkinsert [message #342236 is a reply to message #342235] Thu, 21 August 2008 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Using Bulkinsert [message #342322 is a reply to message #342235] Fri, 22 August 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is an error at line 42.

Regards
Michel
Re: Using Bulkinsert [message #342325 is a reply to message #342235] Fri, 22 August 2008 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
thalladas wrote on Thu, 21 August 2008 21:32
.please guide me regarding this..



haha.. you are funny.

[Updated on: Fri, 22 August 2008 00:52]

Report message to a moderator

Re: Using Bulkinsert [message #342328 is a reply to message #342325] Fri, 22 August 2008 01:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
./fa/1587/0/

Please follow ./fa/1707/0/

And Format Your Post

Regards,
Rajat
Re: Using Bulkinsert [message #342515 is a reply to message #342328] Fri, 22 August 2008 10:48 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Hey,
I am attaching the formatted copy of Store procedure with this reply.
I tryed to use forall like this
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
Please Help me regarding this issue.
Confused

CREATE TYPE cust_test AS OBJECT
(
COMP_PLAN_CD VARCHAR2(10 CHAR),
COMP_PLAN_NM VARCHAR2(40 CHAR),
COMP_PLAN_ID NUMBER
);
Type created

CREATE TYPE cust_ntt AS TABLE OF cust_test

Type created

Part of procedure,where i want to insert Forall and Bulk collect
nt_cust cust_ntt;

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'));
--
BEGIN -- write_record
OPEN c_plan_fields;
FETCH c_plan_fields INTO v_plan_id, v_plan_cd, v_plan_nm;
CLOSE c_plan_fields;
forall i in 1.. nt_cust.count
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,
v_plan_id,
v_plan_cd,
v_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;
  • Attachment: SP.txt
    (Size: 36.94KB, Downloaded 155 times)
Re: Using Bulkinsert [message #342518 is a reply to message #342515] Fri, 22 August 2008 10:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, not sure If I get it right, but why did you use the "nt_cust" array in the FORALL but then you didnt use its values in your insert statement?
Re: Using Bulkinsert [message #342521 is a reply to message #342518] Fri, 22 August 2008 11:20 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
I have only 3 columns that need to be inserted(coming for the cursor)..am starter in writing the code pl/sql...so thats am here..waitng for your valueable reponses.
Re: Using Bulkinsert [message #342522 is a reply to message #342235] Fri, 22 August 2008 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://asktom.oracle.com has many fine coding examples where you can samples using FORALL, etc.
Re: Using Bulkinsert [message #342524 is a reply to message #342521] Fri, 22 August 2008 11:37 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
not sure on this, but based on this error you posted:

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

I created a sample:



SQL> select * from t;

         X          Y
---------- ----------
         1          2
         2          8

SQL> declare
  2    type tt is table of t.x%type index by binary_integer;
  3    vt tt;
  4   begin
  5     select (x +  1) bulk collect into vt from t;
  6
  7     forall i in vt.first..vt.last
  8       insert into t values (3, 6);
  9   end;
 10   /
          insert into t values (3, 6);
          *
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

SQL> declare
  2    type tt is table of t.x%type index by binary_integer;
  3    vt tt;
  4   begin
  5     select (x +  1) bulk collect into vt from t;
  6
  7     forall i in vt.first..vt.last
  8       insert into t values (vt(i), 6);
  9   end;
 10   /

PL/SQL procedure successfully completed.

SQL> select * from t;

         X          Y
---------- ----------
         1          2
         2          8
         2          6
         3          6



Your FORALL doesnt use the array in the INSERT statement.
Re: Using Bulkinsert [message #342550 is a reply to message #342524] Fri, 22 August 2008 14:39 Go to previous message
thalladas
Messages: 16
Registered: August 2008
Junior Member
now my code works.Its compling but,how to use forall in this context as they are more than 3 columns we are inserting.i have seen examples,but its didnt say anything like mine procedure which has more insert columsn than the bulk collect columns....
please help me regarding this.

new code
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;
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,
v_plan_id,
v_plan_cd,
v_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;
Previous Topic: Reading images raises errors ! HOw to handle !?
Next Topic: Wrong number or types of arguments error
Goto Forum:
  


Current Time: Wed Dec 07 18:52:36 CST 2016

Total time taken to generate the page: 0.06607 seconds