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 Go to next message
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 #343667 is a reply to message #343666] Wed, 27 August 2008 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 forall and bulk collect together [message #343668 is a reply to message #343666] Wed, 27 August 2008 15:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still waiting for you to format your post and follow the rules as already asked many times in your previous topic.

Regards
Michel
Re: using forall and bulk collect together [message #343674 is a reply to message #343668] Wed, 27 August 2008 16:15 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
I attached the formatted procedure.
when i posting the message in here ,all the formatting is removed byitself .Should i set some settings in my account.?

Sorry for the inconvienence.Whenever i posted a message i format it,but i dont see the same while its posted on the forum.

  • Attachment: SP1.txt
    (Size: 2.20KB, Downloaded 147 times)

[Updated on: Wed, 27 August 2008 16:20]

Report message to a moderator

Re: using forall and bulk collect together [message #343675 is a reply to message #343674] Wed, 27 August 2008 16:16 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
If you take the time to read the forum guidelines, all will be revealed.
Re: using forall and bulk collect together [message #343684 is a reply to message #343675] Wed, 27 August 2008 16:46 Go to previous messageGo to next message
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;

Re: using forall and bulk collect together [message #343699 is a reply to message #343666] Wed, 27 August 2008 18:15 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
forgive me, but I just have to ask the following question.
Why are you doing this in PL/SQL as opposed to vanilla SQL?
Previous Topic: Passing parameter from Crystal to Oracle
Next Topic: Parallel query returns wrong results
Goto Forum:
  


Current Time: Sat Dec 10 18:24:04 CST 2016

Total time taken to generate the page: 0.16012 seconds