SQL & PL/SQL

Hello guys, my problem is the next following:
Firts at all i will give you a little detail of my enviroment:

So bassically i have a RedHat Release 3 Update 4, my database is a Oracle 10G version 10.2.0.4.

I have to load the oracle database by a heterogenous services, from a SQL server over Windows to my database over Linux, i test all the configuration for that and everything works fine.

So i create a Store Procedure to load all the data from the SQL Server to Oracle like this, this is the source code:

create or replace procedure load_ofsa_tables 
is 
  type table_array is table of fem_checking%rowtype index by pls_integer; 
  v_data table_array; 
  v_date varchar2(20); 
  n_limit number := 100;  
  errores number; 
  err  number; 
  dmlerror_msg exception; 
  sqlerror_msg varchar2(120):= null; 
  error_counter number := 0; 
  pragma exception_init(dmlerror_msg, -24381); 
  cursor c_tbl_select is select * from ofsa_fem_checking@mshs; 
begin 
    ----creating tables to save the bad records 
    execute immediate 'drop table target_err'; 
    execute immediate 'create table target_err as (select * from fem_checking where rownum = 0)'; 
    execute immediate 'commit'; 
open c_tbl_select; 
  loop 
    fetch c_tbl_select bulk collect into v_data limit n_limit; 
        begin 
          forall i in v_data.first..v_data.last 
            save exceptions 
                insert into fem_checking values v_data(i); 
                execute immediate 'commit'; 
        exception 
        when dmlerror_msg then 
            errores := sql%bulk_exceptions.count; 
            error_counter := error_counter + errores; 
                for i in 1..errores loop 
                  dbms_output.put_line(''); 
                  dbms_output.put_line ('the following errors were found: '||sql%bulk_exceptions(i).error_index||':'||sql%bulk_exceptions(i).error_code); 
                  err:= sql%bulk_exceptions(i).error_index; 
                  insert into target_err values v_data(err); 
                  dbms_output.put_line(''); 
                  dbms_output.put_line(sql%bulk_exceptions(i).error_index || ', ' ||sqlerrm(-sql%bulk_exceptions(i).error_code)); 
                end loop; 
        when others then 
          sqlerror_msg := sqlerrm; 
          dbms_output.put_line ('the following errors were found '||chr(13)||sqlerror_msg); 
          dbms_output.put_line(''); 
        end; 
    exit when c_tbl_select%notfound; 
  end loop; 
    commit; 
  close c_tbl_select; 
  v_date := to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'); 
  dbms_output.put_line ('load process complete at: '||chr(9)||v_date); 
  dbms_output.put_line ('total errors'||error_counter); 
end load_ofsa_tables; 

So if you see i made a SELECT * FROM the source table but this SELECT is the one who blow out the memory i set the limit to 100 the prevent that but i don't know wha i am doing wrong, the source table has 174 columns equal than the destiny table, i don't know what to do i test all the ways even using a ROWNUM < 25 for the cursor query and blow out my server, so i will appreciate your advices, and your help.

Thanks,

Carlos Robles

Edited by: Carlos Robles on 28-ago-2009 8:59

Edited by: Carlos Robles on 28-ago-2009 9:00