Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Victor.

Asked: May 20, 2024 - 5:02 pm UTC

Last updated: May 21, 2024 - 7:06 am UTC

Version: 11g

Viewed 100+ times

You Asked

Hi TOM:

I have to copy an AS400 table, for that i have a DBLINK that connects the as400 database to my Oracle 11g

Since it has several millions records, i tried with a bulk collect:

CREATE TABLE AS400_VPRA_ABONO
   ("ABON_NUM_CCTE" NUMBER(9,0) NOT NULL ENABLE, 
 "ABON_FEC_COMPR_PAG" NUMBER(9,0) NOT NULL ENABLE, 
 "ABON_CORR_COMPR" NUMBER(3,0) NOT NULL ENABLE, 
 "ABON_CORRELATIVO" NUMBER(3,0) NOT NULL ENABLE, 
 "ABON_FEC_CPBTE_ING_EGR" NUMBER(9,0) NOT NULL ENABLE, 
 "ABON_TIPO_REND" NUMBER(2,0) NOT NULL ENABLE, 
 "ABON_NUM_CPBTE_ING_EGR" NUMBER(8,0) NOT NULL ENABLE, 
 "ABON_TIPO_COMPR" NUMBER(1,0) NOT NULL ENABLE, 
 "ABON_TIPO_AVISO" NUMBER(2,0) NOT NULL ENABLE, 
 "ABON_NUM_AVISO" NUMBER(8,0) NOT NULL ENABLE, 
 "ABON_LINEA" NUMBER(5,0) NOT NULL ENABLE, 
 "ABON_TIPO_ABONO" NUMBER(2,0) NOT NULL ENABLE, 
 "ABON_TIPO_VIA" NUMBER(1,0) NOT NULL ENABLE, 
 "ABON_RECAUDADOR" NUMBER(5,0) NOT NULL ENABLE, 
 "ABON_MTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_MTO_PAG_PESOS" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_FEC_PAGO" NUMBER(9,0) NOT NULL ENABLE, 
 "ABON_MEDIO_PAGO" NUMBER(1,0) NOT NULL ENABLE, 
 "ABON_AREA" CHAR(16 BYTE) NOT NULL ENABLE, 
 "ABON_BCO_ADM" CHAR(10 BYTE) NOT NULL ENABLE, 
 "ABON_MTO_DEV_MON" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_MTO_PAG_MON_AJ" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_MTO_PAG_PESOS_AJ" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_MOTIVO" NUMBER(3,0) NOT NULL ENABLE, 
 "ABON_SALDO" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_STATUS" NUMBER(2,0) NOT NULL ENABLE, 
 "ABON_STA_FACTUR" CHAR(1 BYTE) NOT NULL ENABLE, 
 "ABON_EXENTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_AFECTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_DEREMI_PAG_MON" NUMBER(9,2) NOT NULL ENABLE, 
 "ABON_IMPTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, 
 "ABON_TIPDOC" CHAR(2 BYTE) NOT NULL ENABLE, 
 "ABON_NUMDOC" CHAR(8 BYTE) NOT NULL ENABLE, 
 "ABON_FILLER" CHAR(14 BYTE) NOT NULL ENABLE
   );

CREATE TABLE AS400_VPRA_ABONO_ORIGIN 
   ("ABON_NUM_CCTE" NUMBER(9,0) NOT NULL ENABLE,  
 "ABON_FEC_COMPR_PAG" NUMBER(9,0) NOT NULL ENABLE,  
 "ABON_CORR_COMPR" NUMBER(3,0) NOT NULL ENABLE,  
 "ABON_CORRELATIVO" NUMBER(3,0) NOT NULL ENABLE,  
 "ABON_FEC_CPBTE_ING_EGR" NUMBER(9,0) NOT NULL ENABLE,  
 "ABON_TIPO_REND" NUMBER(2,0) NOT NULL ENABLE,  
 "ABON_NUM_CPBTE_ING_EGR" NUMBER(8,0) NOT NULL ENABLE,  
 "ABON_TIPO_COMPR" NUMBER(1,0) NOT NULL ENABLE,  
 "ABON_TIPO_AVISO" NUMBER(2,0) NOT NULL ENABLE,  
 "ABON_NUM_AVISO" NUMBER(8,0) NOT NULL ENABLE,  
 "ABON_LINEA" NUMBER(5,0) NOT NULL ENABLE,  
 "ABON_TIPO_ABONO" NUMBER(2,0) NOT NULL ENABLE,  
 "ABON_TIPO_VIA" NUMBER(1,0) NOT NULL ENABLE,  
 "ABON_RECAUDADOR" NUMBER(5,0) NOT NULL ENABLE,  
 "ABON_MTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_MTO_PAG_PESOS" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_FEC_PAGO" NUMBER(9,0) NOT NULL ENABLE,  
 "ABON_MEDIO_PAGO" NUMBER(1,0) NOT NULL ENABLE,  
 "ABON_AREA" CHAR(16 BYTE) NOT NULL ENABLE,  
 "ABON_BCO_ADM" CHAR(10 BYTE) NOT NULL ENABLE,  
 "ABON_MTO_DEV_MON" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_MTO_PAG_MON_AJ" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_MTO_PAG_PESOS_AJ" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_MOTIVO" NUMBER(3,0) NOT NULL ENABLE,  
 "ABON_SALDO" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_STATUS" NUMBER(2,0) NOT NULL ENABLE,  
 "ABON_STA_FACTUR" CHAR(1 BYTE) NOT NULL ENABLE,  
 "ABON_EXENTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_AFECTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_DEREMI_PAG_MON" NUMBER(9,2) NOT NULL ENABLE,  
 "ABON_IMPTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE,  
 "ABON_TIPDOC" CHAR(2 BYTE) NOT NULL ENABLE,  
 "ABON_NUMDOC" CHAR(8 BYTE) NOT NULL ENABLE,  
 "ABON_FILLER" CHAR(14 BYTE) NOT NULL ENABLE 
   );


declare
   type array_object is table of as400_VPRA_ABONO%rowtype index by binary_integer;
 
   cursor c is select * from AS400_VPRA_ABONO_ORIGIN -- VPRA.ABONO@DBLINK_AS400 --> this is the original source of data  
                 Where abon_fec_pago between 20030000 and 20040000; 
   lr_datos array_object;
begin
   open c;
   loop
  fetch c bulk collect into lr_datos limit 100;
  begin
  forall i in 1..lr_datos.count save exceptions
   insert /*+APPEND NOLOGING*/ into as400_VPRA_ABONO values lr_datos (i);
  commit;
  exception
   when others then
   dbms_output.put_line (sqlerrm);
  end;
  exit when c%notfound;
   commit;
 end loop;
 close c;
 exception
 when others then
  dbms_output.put_line (sqlerrm);
 end;
/


But only copy a 100 records, when modify the limit clause, it brings me more... that is not how this should work. What is happening?

Thank you in advanced

Best regards
Victor Usuy

with LiveSQL Test Case:

and Connor said...

This is exactly how the limit clause is meant to work. The idea around LIMIT is that if you try bulk collect 100 billion rows, then you need to store 100 billion rows in memory and boom...there goes your server!

For your particular case above, I would just do

insert /*+APPEND */ into as400_VPRA_ABONO
select * from AS400_VPRA_ABONO_ORIGIN -- VPRA.ABONO@DBLINK_AS400 --> this is the original source of data  
                 Where abon_fec_pago between 20030000 and 20040000; 



More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here