bulk collect (insert data) into oracle tables
I have a script and need to use bulk collect
Help is appreciated.
DECLARE
CURSOR c_progpcon
IS
SELECT cust_id
,div_no
FROM dss.program_processing_controls;
CURSOR c_custord(in_orgrole_id_customer IN dss.orders.orgrole_id_customer%TYPE)
IS
SELECT id order_id
,order_type
,order_number
,customer_po
FROM dss.orders
WHERE order_type = 'CUST'
AND orgrole_id_customer = in_orgrole_id_customer;
CURSOR c_outbound(in_order_id IN dss.orders.id%TYPE)
IS
SELECT ship.id ship_id
,ship.shipper_no
,shptrk.id shptrk_id
,shptrk.waybill
,shptrk.estimated_freight
,shptrk.actual_freight
,shptrk.dt_created
FROM dss.shipments ship
,dss.shipment_trackings shptrk
WHERE ship.order_id = in_order_id
AND shptrk.ship_id = ship.id;
v_cust_processed NUMBER := 0;
v_custord_processed NUMBER := 0;
v_orgrole_id_customer dss.org_roles.id%TYPE;
v_estimated_freight_custord adwaram.order_freight.estimated_freight%TYPE;
v_actual_freight_custord adwaram.order_freight.actual_freight%TYPE;
v_calc_freight number:=0;
v_method varchar2(4000);
BEGIN
FOR c_progpcon_rec IN c_progpcon
LOOP
v_cust_processed := v_cust_processed + 1;
SELECT orgrole_id
INTO v_orgrole_id_customer
FROM dss.customers
WHERE id = c_progpcon_rec.cust_id;
FOR c_custord_rec IN c_custord(v_orgrole_id_customer)
LOOP
v_custord_processed := v_custord_processed + 1;
-- outbound customer order
FOR c_outbound_rec IN c_outbound(c_custord_rec.order_id)
LOOP
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
(null,c_outbound_rec.ship_id,v_method);
exception
when others then
v_calc_freight := 0;
end;
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(c_custord_rec.order_type
,c_custord_rec.order_number
,c_outbound_rec.shipper_no
,c_outbound_rec.waybill
,c_outbound_rec.actual_freight
,v_calc_freight--c_outbound_rec.estimated_freight
,c_outbound_rec.dt_created
,c_custord_rec.order_id
,c_outbound_rec.ship_id
,c_outbound_rec.shptrk_id
,NULL
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'OUTBOUND CUST ORDER'
,SYSDATE);
END LOOP;
END LOOP;
END LOOP;
END;Help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
FORALL i1 IN 1 .. c_outbound_tab.count
Should I just use FOR
Should I just use FOR
ASKER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
Is oracle version the problem?
Is oracle version the problem?
>>I have a script and need to use bulk collect
What makes you think bulk collect will make things better? That typically is used when you need to access the same result set multiple times.
I've been involved in all the related questions and I don't think you are processing the same result set more than once. So, I'm not sure bulk collect buys you the performance you are wanting.
>>Is oracle version the problem?
FORALL was available in 10g.
What makes you think bulk collect will make things better? That typically is used when you need to access the same result set multiple times.
I've been involved in all the related questions and I don't think you are processing the same result set more than once. So, I'm not sure bulk collect buys you the performance you are wanting.
>>Is oracle version the problem?
FORALL was available in 10g.
FORALL was available in 10g, but a search for the PLS-00436 reveals version related issues.
Workaround mentioned there is to define de pl-sql collection by means of oracle-types (and remove the type definition in the pl-sql block)
create type c_outbound_rec_type as
(ship_id number
, ....
,dt_created date
);
CREATE TYPE c_outbound_tab_type AS TABLE OF c_outbound_rec_type;
But I have no older oracle version available to check if this wil work for you.
You can create a for loop to do the insert but for performance the forall insert wil be more rewarding then the bulk collect.
Workaround mentioned there is to define de pl-sql collection by means of oracle-types (and remove the type definition in the pl-sql block)
create type c_outbound_rec_type as
(ship_id number
, ....
,dt_created date
);
CREATE TYPE c_outbound_tab_type AS TABLE OF c_outbound_rec_type;
But I have no older oracle version available to check if this wil work for you.
You can create a for loop to do the insert but for performance the forall insert wil be more rewarding then the bulk collect.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My modified code. This works fine and only takes 1 minute and 15 secs.
prompt Querying freight ...
SET serveroutput ON
DECLARE
CURSOR c_progpcon
IS
SELECT cust_id
,div_no
FROM dss.program_processing_controls;
--where cust_id=9677660176; testing
CURSOR c_custord(in_orgrole_id_customer IN dss.orders.orgrole_id_customer%TYPE)
IS
SELECT id order_id
,order_type
,order_number
,customer_po
FROM dss.orders
WHERE order_type = 'CUST'
AND orgrole_id_customer = in_orgrole_id_customer
;
CURSOR c_outbound(in_order_id IN dss.orders.id%TYPE)
IS
SELECT ship.id ship_id
,ship.shipper_no
,shptrk.id shptrk_id
,shptrk.waybill
,shptrk.estimated_freight
,shptrk.actual_freight
,shptrk.dt_created
FROM dss.shipments ship
,dss.shipment_trackings shptrk
WHERE ship.order_id = in_order_id
AND shptrk.ship_id = ship.id
AND shptrk.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy');
CURSOR c_ordsch(in_order_id IN dss.orders.id%TYPE)
IS
SELECT ordsch.id ordsch_id
FROM dss.orders ord
,dss.ordered_items orditm
,dss.ordered_item_schedules ordsch
WHERE ord.id = in_order_id
AND orditm.order_id = ord.id
AND ordsch.orditm_id = orditm.id;
CURSOR c_inbound(in_orditm_id IN dss.ordered_items.id%TYPE)
IS
SELECT recshp.id recshp_id
,recshp.waybill
,recshp.estimated_freight
,recshp.actual_freight
,recshp.dt_created
FROM dss.built_items bltitm
,dss.received_shipments recshp
WHERE bltitm.orditm_id_rcvd = in_orditm_id
AND recshp.id = bltitm.recshp_id
AND recshp.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy')
UNION ALL
SELECT recshp.id recshp_id
,recshp.waybill
,recshp.estimated_freight
,recshp.actual_freight
,recshp.dt_created
FROM dss.received_items rcvitm
,dss.received_shipments recshp
WHERE rcvitm.orditm_id_rcvd = in_orditm_id
AND recshp.id = rcvitm.recshp_id
AND recshp.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy');
v_cust_processed NUMBER := 0;
v_custord_processed NUMBER := 0;
v_orgrole_id_customer dss.org_roles.id%TYPE;
v_estimated_freight_custord adwaram.order_freight.estimated_freight%TYPE;
v_actual_freight_custord adwaram.order_freight.actual_freight%TYPE;
v_orditm_id_core dss.exchange_cores.orditm_id%TYPE;
v_order_id_core dss.orders.id%TYPE;
v_bltitm_id_core dss.po_histories.bltitm_id%TYPE;
v_order_type dss.orders.order_type%TYPE;
v_order_number dss.orders.order_number%TYPE;
v_order_id_xfer dss.orders.id%TYPE;
v_order_id_inbound dss.orders.id%TYPE;
v_orditm_id_po ordered_items.id%TYPE;
--anu
v_calc_freight number:=0;
v_method varchar2(4000);
c_limit PLS_INTEGER := 100;
TYPE order_info_rec IS RECORD
(
order_id orders.id%TYPE,
order_type orders.order_type%type,
order_number orders.order_number%type,
customer_po orders.customer_po%type
);
TYPE order_info_t IS TABLE OF order_info_rec;
l_ord_cust order_info_t;
l_order_info order_info_t;
TYPE frt_table_type IS TABLE OF adwaram.order_freight%ROWTYPE INDEX BY PLS_INTEGER;
l_data frt_table_type;
lcnt NUMBER:=0;
BEGIN
execute immediate 'TRUNCATE TABLE adwaram.order_freight';
FOR c_progpcon_rec IN c_progpcon
LOOP
v_cust_processed := v_cust_processed + 1;
SELECT orgrole_id
INTO v_orgrole_id_customer
FROM dss.customers
WHERE id = c_progpcon_rec.cust_id;
--FOR c_custord_rec IN c_custord(v_orgrole_id_customer)
BEGIN
OPEN c_custord(v_orgrole_id_customer);
LOOP
FETCH c_custord--(v_orgrole_id_customer)
BULK COLLECT INTO l_order_info
LIMIT c_limit;
EXIT WHEN l_order_info.COUNT = 0;
-- dbms_output.put_line('Info Count - '||l_order_info.count);
for i in 1.. l_order_info.count
loop
v_custord_processed := v_custord_processed + 1;
-- outbound customer order
--FOR c_outbound_rec IN c_outbound(c_custord_rec.order_id)
FOR c_outbound_rec IN c_outbound(l_order_info(i).order_id)
LOOP
lcnt:=lcnt+1;
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
(null,c_outbound_rec.ship_id,v_method);
exception
when others then
v_calc_freight := 0;
end;
l_data(lcnt).order_type :=l_order_info(i).order_type;
l_data(lcnt).order_number :=l_order_info(i).order_number;
l_data(lcnt).shipper_no :=c_outbound_rec.shipper_no;
l_data(lcnt).waybill :=c_outbound_rec.waybill;
l_data(lcnt).actual_freight :=c_outbound_rec.actual_freight;
l_data(lcnt).estimated_freight :=v_calc_freight;
l_data(lcnt).waybill_entered :=c_outbound_rec.dt_created;
l_data(lcnt).order_id :=l_order_info(i).order_id;
l_data(lcnt).ship_id :=c_outbound_rec.ship_id;
l_data(lcnt).shptrk_id :=c_outbound_rec.shptrk_id;
l_data(lcnt).recshp_id :=null;
l_data(lcnt).cust_id :=c_progpcon_rec.cust_id;
l_data(lcnt).order_id_cust :=l_order_info(i).order_id;
l_data(lcnt).notes :='OUTBOUND CUST ORDER';
l_data(lcnt).dt_created :=SYSDATE;
l_data(lcnt).out_method :=v_method;
END LOOP;
FOR c_ordsch_rec IN c_ordsch(l_order_info(i).order_id)
LOOP
-- get core
BEGIN
SELECT xccore.orditm_id
,pohist.bltitm_id
INTO v_orditm_id_po
,v_bltitm_id_core
FROM dss.exchange_units xcunit
,dss.exchange_cores xccore
,dss.po_histories pohist
WHERE xcunit.ordsch_id = c_ordsch_rec.ordsch_id
AND xccore.xcitm_id = xcunit.xcitm_id
AND pohist.orditm_id(+) = xccore.orditm_id;
IF v_bltitm_id_core IS NOT NULL
THEN
v_order_id_core := dss.pkg_inven.func_get_order(v_bltitm_id_core
,'ORDER_ID');
v_orditm_id_core := dss.pkg_inven.func_get_order(v_bltitm_id_core
,'ORDITM_ID');
ELSE
v_order_id_core := NULL;
END IF;
IF v_order_id_core IS NOT NULL
THEN
-- outbound order for received core (repair order or customer order)
FOR c_outbound_rec IN c_outbound(v_order_id_core)
LOOP
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
(null,c_outbound_rec.ship_id,v_method);
exception
when others then
v_calc_freight := 0;
end;
SELECT order_type
,order_number
INTO v_order_type
,v_order_number
FROM dss.orders
WHERE id = v_order_id_core;
lcnt:=lcnt+1;
l_data(lcnt).order_type :=v_order_type;
l_data(lcnt).order_number :=v_order_number;
l_data(lcnt).shipper_no :=c_outbound_rec.shipper_no;
l_data(lcnt).waybill :=c_outbound_rec.waybill;
l_data(lcnt).actual_freight :=c_outbound_rec.actual_freight;
l_data(lcnt).estimated_freight :=v_calc_freight;
l_data(lcnt).waybill_entered :=c_outbound_rec.dt_created;
l_data(lcnt).order_id :=v_order_id_core;
l_data(lcnt).ship_id :=c_outbound_rec.ship_id;
l_data(lcnt).shptrk_id :=c_outbound_rec.shptrk_id;
l_data(lcnt).recshp_id :=null;
l_data(lcnt).cust_id :=c_progpcon_rec.cust_id;
l_data(lcnt).order_id_cust :=l_order_info(i).order_id;
l_data(lcnt).notes :='OUTBOUND '||v_order_type||' ORDER';
l_data(lcnt).dt_created :=SYSDATE;
l_data(lcnt).out_method :=v_method;
END LOOP;
END IF;
-- xfer related to customer order
BEGIN
SELECT ord.id
INTO v_order_id_xfer
FROM dss.orders ord
,dss.ordered_items orditm
WHERE ord.order_type = 'XFER'
AND ord.div_no = c_progpcon_rec.div_no
AND orditm.order_id = ord.id
AND orditm.customer_po = l_order_info(i).customer_po;
FOR c_outbound_rec IN c_outbound(v_order_id_xfer)
LOOP
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
( null,c_outbound_rec.ship_id,v_method);
exception
when others then
v_calc_freight := 0;
end;
SELECT order_type
,order_number
INTO v_order_type
,v_order_number
FROM dss.orders
WHERE id = v_order_id_xfer;
lcnt:=lcnt+1;
l_data(lcnt).order_type :=v_order_type;
l_data(lcnt).order_number :=v_order_number;
l_data(lcnt).shipper_no :=c_outbound_rec.shipper_no;
l_data(lcnt).waybill :=c_outbound_rec.waybill;
l_data(lcnt).actual_freight :=c_outbound_rec.actual_freight;
l_data(lcnt).estimated_freight :=v_calc_freight;
l_data(lcnt).waybill_entered :=c_outbound_rec.dt_created;
l_data(lcnt).order_id :=v_order_id_xfer;
l_data(lcnt).ship_id :=c_outbound_rec.ship_id;
l_data(lcnt).shptrk_id :=c_outbound_rec.shptrk_id;
l_data(lcnt).recshp_id :=null;
l_data(lcnt).cust_id :=c_progpcon_rec.cust_id;
l_data(lcnt).order_id_cust :=l_order_info(i).order_id;
l_data(lcnt).notes :='OUTBOUND '||v_order_type||' ORDER';
l_data(lcnt).dt_created :=SYSDATE;
l_data(lcnt).out_method :=v_method;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
OR TOO_MANY_ROWS
THEN
NULL;
END;
-- inbound orders associate with exchange - v_orditm_id_core (if ro)
-- v_orditm_id_po (csp po)
IF v_orditm_id_core IS NOT NULL
THEN
FOR c_inbound_rec IN c_inbound(v_orditm_id_core)
LOOP
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
( c_inbound_rec.recshp_id,null,v_method);
exception
when others then
v_calc_freight := 0;
end;
SELECT ord.order_type
,ord.order_number
,ord.id
INTO v_order_type
,v_order_number
,v_order_id_inbound
FROM dss.ordered_items orditm
,dss.orders ord
WHERE orditm.id = v_orditm_id_core
AND ord.id = orditm.order_id;
lcnt:=lcnt+1;
l_data(lcnt).order_type :=v_order_type;
l_data(lcnt).order_number :=v_order_number;
l_data(lcnt).shipper_no :=NULL;
l_data(lcnt).waybill :=c_inbound_rec.waybill;
l_data(lcnt).actual_freight :=c_inbound_rec.actual_freight;
l_data(lcnt).estimated_freight :=v_calc_freight;
l_data(lcnt).waybill_entered :=c_inbound_rec.dt_created;
l_data(lcnt).order_id :=v_order_id_inbound;
l_data(lcnt).ship_id :=NULL;
l_data(lcnt).shptrk_id :=NULL;
l_data(lcnt).recshp_id :=c_inbound_rec.recshp_id;
l_data(lcnt).cust_id :=c_progpcon_rec.cust_id;
l_data(lcnt).order_id_cust :=l_order_info(i).order_id;
l_data(lcnt).notes :='INBOUND '||v_order_type||' ORDER';
l_data(lcnt).dt_created :=SYSDATE;
l_data(lcnt).out_method :=v_method;
END LOOP;
END IF;
IF v_orditm_id_po IS NOT NULL
THEN
FOR c_inbound_rec IN c_inbound(v_orditm_id_po)
LOOP
begin
v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
( c_inbound_rec.recshp_id
,NULL
, v_method
);
exception
when others then
v_calc_freight := 0;
end;
SELECT ord.order_type
,ord.order_number
,ord.id
INTO v_order_type
,v_order_number
,v_order_id_inbound
FROM dss.ordered_items orditm
,dss.orders ord
WHERE orditm.id = v_orditm_id_po
AND ord.id = orditm.order_id;
lcnt:=lcnt+1;
l_data(lcnt).order_type :=v_order_type;
l_data(lcnt).order_number :=v_order_number;
l_data(lcnt).shipper_no :=NULL;
l_data(lcnt).waybill :=c_inbound_rec.waybill;
l_data(lcnt).actual_freight :=c_inbound_rec.actual_freight;
l_data(lcnt).estimated_freight :=v_calc_freight;
l_data(lcnt).waybill_entered :=c_inbound_rec.dt_created;
l_data(lcnt).order_id :=v_order_id_inbound;
l_data(lcnt).ship_id :=NULL;
l_data(lcnt).shptrk_id :=NULL;
l_data(lcnt).recshp_id :=c_inbound_rec.recshp_id;
l_data(lcnt).cust_id :=c_progpcon_rec.cust_id;
l_data(lcnt).order_id_cust :=l_order_info(i).order_id;
l_data(lcnt).notes :='INBOUND '||v_order_type||' ORDER';
l_data(lcnt).dt_created :=SYSDATE;
l_data(lcnt).out_method :=v_method;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
OR TOO_MANY_ROWS
THEN
NULL;
END;
END LOOP;
END LOOP;
END LOOP;
CLOSE c_custord;
END;
END LOOP;
/* Record-based insert and subset of columns... */
FORALL i IN 1 .. l_data.COUNT
INSERT INTO (SELECT order_type, order_number,
shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,notes
,order_id_cust
,dt_created
,out_method
from adwaram.order_freight
)
VALUES l_data(i);
COMMIT;
dbms_output.put_line(TO_CHAR(v_cust_processed)||' customers processed.');
dbms_output.put_line(TO_CHAR(v_custord_processed)||' customer orders processed.');
dbms_output.put_line('Total in the pl/sql table - '||l_data.count);
END;
/ASKER
thanks. I had to change few things.
ASKER
ORA-06550: line 158, column 17:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 158, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 159, column 17:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records