Home » SQL & PL/SQL » SQL & PL/SQL » Using bulk collect and forall syntax in the procedure (oracle9i)
Using bulk collect and forall syntax in the procedure [message #320488] Thu, 15 May 2008 04:48 Go to next message
saurav.raj
Messages: 8
Registered: May 2008
Location: BANGALORE
Junior Member

Hi
i want to modify the following procedure with the help of bulk collect and forall operator.....

can any one help me of doing it.......

CURSOR cur_merch_loc IS
SELECT
merch_loc_name
,product_name
,outlet_name
,olet_loc
,ret_audit_flag
FROM
V3_BC_MERCH_ASSORTMENT
MINUS
SELECT
merch_loc_name
,product_name
,outlet_name
,olet_loc
,ret_audit_flag
FROM
siebeletl.bc_populate_merch_assort@sibdb;

const_def_org VARCHAR2(100) := 'Default Organization';
record_counter NUMBER := 0;
row_id_gen NUMBER := 1;


BEGIN

DELETE eim_prodinvloc
WHERE if_row_batch_num
BETWEEN p_if_row_batch_num_min
AND p_if_row_batch_num_max;

COMMIT;

FOR rec_merch_loc IN cur_merch_loc
LOOP

INSERT INTO eim_prodinvloc
(
row_id,
if_row_stat,
if_row_batch_num,
pil_invloc_bu,
pil_invloc_name,
pil_prod_bu,
pil_prod_name,
pil_cg_active_flg,
pil_cgrtlaudit_flg,
cg_op_acc_bu,
cg_op_acc_loc,
cg_op_acc_name,
cg_op_prod_bu,
cg_op_prod_name
)
VALUES (
row_id_gen,
p_eim_string,
p_if_row_batch_num_min + FLOOR(row_id_gen/p_def_batch_num),
const_def_org,
rec_merch_loc.merch_loc_name,
const_def_org,
rec_merch_loc.product_name,
'Y',
rec_merch_loc.ret_audit_flag,
const_def_org,
rec_merch_loc.olet_loc,
rec_merch_loc.outlet_name,
const_def_org,
rec_merch_loc.product_name
);

record_counter := record_counter + 1;
row_id_gen := row_id_gen + 1;

IF (record_counter = p_def_batch_num ) THEN

COMMIT;
record_counter := 0;

END IF;

END LOOP;

COMMIT;

p_status := 0;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_status := 1;
p_message := 'PROC_BC_POPULATE_MERCH_ASSORT : ' || SQLERRM;

END;
/
Re: Using bulk collect and forall syntax in the procedure [message #320489 is a reply to message #320488] Thu, 15 May 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is time now for you read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

You don't need bulk collect and forall, just execute "insert select".

Regards
Michel
Re: Using bulk collect and forall syntax in the procedure [message #320492 is a reply to message #320488] Thu, 15 May 2008 04:55 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a better plan.
Replace the whole lot with this (untested):
const_def_org VARCHAR2(100) := 'Default Organization';

BEGIN

DELETE eim_prodinvloc
WHERE if_row_batch_num
BETWEEN p_if_row_batch_num_min
AND p_if_row_batch_num_max;

COMMIT;

INSERT INTO eim_prodinvloc
(
row_id,
if_row_stat,
if_row_batch_num,
pil_invloc_bu,
pil_invloc_name,
pil_prod_bu,
pil_prod_name,
pil_cg_active_flg,
pil_cgrtlaudit_flg,
cg_op_acc_bu,
cg_op_acc_loc,
cg_op_acc_name,
cg_op_prod_bu,
cg_op_prod_name
)
 (SELECT rownum,
         p_eim_string
         p_if_row_batch_num_min + FLOOR(rownum/p_def_batch_num),
         const_def_org,
         merch_loc_name,
         const_def_org,
         product_name,
         'Y',
         ret_audit_flag,
         const_def_org,
         olet_loc,
         outlet_name,
         const_def_org,
         product_name
FROM (SELECT merch_loc_name
            ,product_name
            ,outlet_name
            ,olet_loc
            ,ret_audit_flag
      FROM   V3_BC_MERCH_ASSORTMENT
     MINUS
      SELECT merch_loc_name
            ,product_name
            ,outlet_name
            ,olet_loc
            ,ret_audit_flag
      FROM   siebeletl.bc_populate_merch_assort@sibdb));

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_status := 1;
p_message := 'PROC_BC_POPULATE_MERCH_ASSORT : ' || SQLERRM;

END;
Previous Topic: Type object declaration for cur% rowtype
Next Topic: PL/SQL: ORA-04052: error
Goto Forum:
  


Current Time: Wed Dec 07 02:51:07 CST 2016

Total time taken to generate the page: 0.10926 seconds