bulk collect (insert data) into oracle tables

I have a script and need to use bulk collect

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;

Open in new window


Help is appreciated.
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
-- you do nothing with v_method  :  depending on what the function DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT does you could omit the most inner loop and change it to
insert into ...  select

-- but if you want  the bulk collect / bulk insert  try this script (not tested)

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);
   
   type c_outbound_tab_type is table of c_outbound%rowtype index by pls_integer;  -- define tabeltype based on cursor
   c_outbound_tab c_outbound_tab_type; --  define PS/SQL tabel


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

         -- fill pl/sql table
         OPEN c_outbound(c_custord_rec.order_id);
         FETCH c_outbound BULK COLLECT INTO c_outbound_tab;
         CLOSE c_outbound;
         
         -- calculate estimated_freight
         FOR i1 IN 1 .. c_outbound_tab.count LOOP
                   begin
                    v_calc_freight:=DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT
                             (null,c_outbound_tab(i1).ship_id,v_method);
               exception
                when others then
                v_calc_freight := 0;
          end;
          c_outbound_tab(i1).estimated_freight := v_calc_freight;
         END LOOP;
         
         -- bulk insert
         FORALL i1 IN 1 .. c_outbound_tab.count
            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_tab(i1).shipper_no
               ,c_outbound_tab(i1).waybill
               ,c_outbound_tab(i1).actual_freight
               ,c_outbound_tab(i1).estimated_freight
               ,c_outbound_tab(i1).dt_created
               ,c_custord_rec.order_id
               ,c_outbound_tab(i1).ship_id
               ,c_outbound_tab(i1).shptrk_id
               ,NULL
               ,c_progpcon_rec.cust_id
               ,c_custord_rec.order_id
               ,'OUTBOUND CUST ORDER'
               ,SYSDATE);
         
         
         v_custord_processed := v_custord_processed + SQL%ROWCOUNT;

  END LOOP;
END LOOP;
END;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Error at line 4
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
0
anumosesAuthor Commented:
FORALL i1 IN 1 .. c_outbound_tab.count

Should I just use FOR
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

anumosesAuthor Commented:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

Is oracle version the problem?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
flow01Commented:
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.
0
anumosesAuthor Commented:
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;

Does this have to be after the begin?
0
anumosesAuthor Commented:
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;
/

Open in new window

0
anumosesAuthor Commented:
thanks. I had to change few things.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.