oracle-databaseplsqlbulk-operations

Bulk inserting in Oracle PL/SQL


I have around 5 million of records which needs to be copied from table of one schema to table of another schema(in the same database). I have prepared a script but it gives me the below error.

ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define

Following is my script

DECLARE    
    TYPE tA IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
    TYPE tB IS TABLE OF SchemaA.TableA.band%TYPE INDEX BY PLS_INTEGER;
    TYPE tD IS TABLE OF SchemaA.TableA.start_date%TYPE INDEX BY PLS_INTEGER;
    TYPE tE IS TABLE OF SchemaA.TableA.end_date%TYPE INDEX BY PLS_INTEGER;        
    rA tA;
    rB tB;
    rD tD;
    rE tE;
    f number :=0;    
BEGIN

    SELECT col1||col2||col3 as main_col, band, effective_start_date as start_date, effective_end_date as end_date 
    BULK COLLECT INTO rA, rB, rD, rE 
    FROM schemab.tableb;

    FORALL i IN rA.FIRST..rE.LAST
        insert into SchemaA.TableA(main_col, BAND, user_type, START_DATE, END_DATE, roll_no) 
        values(rA(i), rB(i), 'C', rD(i), rE(i), 71);

    f:=f+1;

    if (f=10000) then
        commit;
    end if;

end;

Could you please help me in finding where the error lies?


Solution

  • Following script worked for me and i was able to load around 5 millions of data within 15 minutes.

     ALTER SESSION ENABLE PARALLEL DML
     /
    
     DECLARE
    
    
     cursor c_p1 is 
        SELECT col1||col2||col3 as main_col, band, effective_start_date as start_date, effective_end_date as end_date 
        FROM schemab.tableb;
    
        TYPE TY_P1_FULL is table of c_p1%rowtype
        index by pls_integer;
    
       v_P1_FULL TY_P1_FULL;
    
       v_seq_num number;
    
    BEGIN
    
    open c_p1;
    
    loop
    
    fetch c_p1 BULK COLLECT INTO v_P1_FULL LIMIT 10000;
    exit when v_P1_FULL.count = 0;
    FOR i IN 1..v_P1_FULL.COUNT loop
    
    
    INSERT /*+ APPEND */ INTO schemaA.tableA VALUES (v_P1_FULL(i));
    
    end loop;
    commit;
    end loop;
    close c_P1;
    dbms_output.put_line('Load completed');
    
    
    end;
    
    -- Disable parallel mode for this session
    ALTER SESSION DISABLE PARALLEL DML
    /