BULK COLLECT insert LIMIT returns ORA-01401: inserted value too large for column

1) You are not doing anything with the V_MLER_BULK "bulk collect"ed rows.
2) You need to use "FORALL...INSERT":

You need to try something like this:

-- Etc --
Open C1;
  Loop
    Fetch C1 Bulk Collect
         Into
              V_Mler_Bulk Limit V_Array_Size;
    Exit When V_Mler_Bulk.Count=0;
 
   Forall I In V_Mler_Bulk.First..V_Mler_Bulk.Last
     Insert Into Mler_Bulk
    (Batchnumber,
     Loadtrackdetailkey,
     Mlfinitestateid,
     Mlfinitestatedesc,
     Mlappliestoallplans,
     Mlloadrecordtomem,
     Creationdate,
     Creationuser,
     Creationprogram,
     Maintenancedate,
     Maintenanceuser,
     Maintenanceprogram)
     Values (
     V_Mler_Bulk(I).Batchnumber,
     V_Mler_Bulk(I).Loadtrackdetailkey,
     V_Mler_Bulk(I).Mlfinitestateid,
     V_Mler_Bulk(I).Mlfinitestatedesc,
     V_Mler_Bulk(I).Mlappliestoallplans,
     V_Mler_Bulk(I).Mlloadrecordtomem,
     V_Mler_Bulk(I).Creationdate,
     V_Mler_Bulk(I).Creationuser,
     V_Mler_Bulk(I).Creationprogram,
     V_Mler_Bulk(I).Maintenancedate,
     V_Mler_Bulk(I).Maintenanceuser,
     V_Mler_Bulk(I).Maintenanceprogram
     ;
    V_Tot_Cnt:=v_Tot_Cnt+V_Mler_Bulk.Count;
    Commit;
    Writelog('Inserted Into Mler_Bulk So Far: '|| V_Tot_Cnt);
 
  End Loop;

Select allOpen in new window

Hi,

could you please give a desc of POST_BULK_MLER_LOG?

To: MikeOM:
he above FORALL IN I code rec'v ' PLS-00436: Implementation restriction: cannot reference fields of BULK In-BIND table of records'.

To: Markusld:

CREATE TABLE post_bulk_mler_log
    (procedure_name                 VARCHAR2(25),
    procedure_section              VARCHAR2(25),
    procedure_message              VARCHAR2(25),
    execution_date                 DATE,
    execution_code                 NUMBER(10,0))
   TABLESPACE metastorm;

I was testing a way to keep a status table. It worked when I just had 1 column in a test table but as soon as I added the real multi-column table I didn't get very far due to the ORA-1401 error.

The procedure_message-field seems to be too small (25 characters, whereas 'Inserted into MLER_BULK so far: '|| V_TOT_CNT has at least 31 characters)

OK, version 9i does not allow referencing  attributes of the array record.
The work-around would be to create individual pl/sql tables for each column.

Another work-around is this:

CREATE OR REPLACE PROCEDURE bulk_collect_into_mler
IS
   CURSOR c1
   IS
      SELECT batchnumber, loadtrackdetailkey, mlfinitestateid,
             mlfinitestatedesc, mlappliestoallplans, mlloadrecordtomem,
             creationdate, creationuser, creationprogram, maintenancedate,
             maintenanceuser, maintenanceprogram
        FROM mler_bulk_arch m
       WHERE m.batchnumber IN (SELECT h.batchnumber
                                 FROM mlheader_arch h);
 
   mler_bulk_rec   c1%ROWTYPE;
 
   TYPE mler_bulk_tab IS TABLE OF mler_bulk_rec
      INDEX BY PLS_INTEGER;
 
   v_mler_bulk     mler_bulk_tab;
   v_array_size    NUMBER        := 10000;
   v_tot_cnt       NUMBER (20)   := 0;
   i               PLS_INTEGER;
 
   PROCEDURE writelog (pmsg IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   -- Writelog
      INSERT INTO post_bulk_mler_log
           VALUES ('Bulk_Collect_Ins_Mler', 'Main', pmsg, SYSDATE, 0);
 
      COMMIT;
   END writelog;
BEGIN
   writelog ('Started');
 
   OPEN c1;
 
   LOOP
      FETCH c1
      BULK COLLECT INTO v_mler_bulk LIMIT v_array_size;
 
      EXIT WHEN v_mler_bulk.COUNT = 0;
 
      BEGIN
         FORALL i IN 1 .. v_mler_bulk.COUNT
            INSERT INTO (SELECT batchnumber, loadtrackdetailkey,
                                mlfinitestateid, mlfinitestatedesc,
                                mlappliestoallplans, mlloadrecordtomem,
                                creationdate, creationuser,
                                creationprogram,maintenancedate,
                                maintenanceuser, maintenanceprogram
                           FROM mler_bulk)
                 VALUES v_mler_bulk (i);
         v_tot_cnt := v_tot_cnt + v_mler_bulk.COUNT;
         COMMIT;
         writelog ('Inserted Into Mler_Bulk So Far: ' || v_tot_cnt);
      END;
 
      COMMIT;
   END LOOP;
 
   CLOSE c1;
 
   writelog ('Finished - Total Rows: ' || v_tot_cnt);
EXCEPTION
   WHEN OTHERS
   THEN
      writelog (SQLERRM);
END;

Select allOpen in new window

Thanks MikeOM.

This is much cleaner and I can understand the logic better.

We're close:
I'm rec'ing error: PLS-00488: invalid variable declaration: object 'MLER_BULK_REC' must be a type or subtype:

CREATE OR REPLACE PROCEDURE bulk_collect_into_mler
IS
   CURSOR c1
   IS
      SELECT batchnumber, loadtrackdetailkey, mlfinitestateid,
             mlfinitestatedesc, mlappliestoallplans, mlloadrecordtomem,
             creationdate, creationuser, creationprogram, maintenancedate,
             maintenanceuser, maintenanceprogram
        FROM mler_bulk_arch m
       WHERE m.batchnumber IN (SELECT h.batchnumber
                                 FROM mlheader_arch h);
 
   mler_bulk_rec   c1%ROWTYPE;
 
   TYPE mler_bulk_tab IS TABLE OF mler_bulk_rec
      INDEX BY PLS_INTEGER;
 
   v_mler_bulk     mler_bulk_tab;
   v_array_size    NUMBER        := 10000;
   v_tot_cnt       NUMBER (20)   := 0;
   i               PLS_INTEGER;
 
   PROCEDURE writelog (pmsg IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   -- Writelog
      INSERT INTO post_bulk_mler_log
           VALUES ('Bulk_Collect_Ins_Mler', 'Main', pmsg, SYSDATE, 0);
 
      COMMIT;
   END writelog;
BEGIN
   writelog ('Started');
 
   OPEN c1;
 
   LOOP
      FETCH c1
      BULK COLLECT INTO v_mler_bulk LIMIT v_array_size;
 
      EXIT WHEN v_mler_bulk.COUNT = 0;
 
      BEGIN
         FORALL i IN 1 .. v_mler_bulk.COUNT
            INSERT INTO (SELECT batchnumber, loadtrackdetailkey,
                                mlfinitestateid, mlfinitestatedesc,
                                mlappliestoallplans, mlloadrecordtomem,
                                creationdate, creationuser,
                                creationprogram,maintenancedate,
                                maintenanceuser, maintenanceprogram
                           FROM mler_bulk)
                 VALUES v_mler_bulk (i);
         v_tot_cnt := v_tot_cnt + v_mler_bulk.COUNT;
         COMMIT;
         writelog ('Inserted Into Mler_Bulk So Far: ' || v_tot_cnt);
      END;
 
      COMMIT;
   END LOOP;
 
   CLOSE c1;
 
   writelog ('Finished - Total Rows: ' || v_tot_cnt);
EXCEPTION
   WHEN OTHERS
   THEN
      writelog (SQLERRM);
END;

markusld:

Thanks for your question on how the post_bulk_mler_log table was built.

It dawned on me the error wasn't caused by the size of the data being inserted in the new mler_bulk table but the size of the procedure_message column - it was only varchar2(25) and I was sending a much longer status definition. I've dropped and re-created the table with a large column - procedure_message varchar2(100).
 
I went back to the one of the original attempts where I was getting a clean compile.

Using the new revised post_mler_bulk_log and executed an insert to mler_bulk 24 million rows within 119 secs!

I still have some tuning/code cleaning up but I'm finally heading in the right direction thanks to all of you:

Here's what I compiled cleanly and executed:

PROCEDURE BULK_COLLECT_INTO_MLER

   IS
 
  TYPE MLER_BULK_REC IS RECORD
       (R_BATCHNUM MLER_BULK.BATCHNUMBER%TYPE,
        R_LOADTRK  MLER_BULK.LOADTRACKDETAILKEY%TYPE,
        R_STATEID  MLER_BULK.MLFINITESTATEID%TYPE,
        R_STATEDE  MLER_BULK.MLFINITESTATEDESC%TYPE,
        R_ALLPLAN  MLER_BULK.MLAPPLIESTOALLPLANS%TYPE,
        R_RECMEM   MLER_BULK.MLLOADRECORDTOMEM%TYPE,
        R_CRDATE   MLER_BULK.CREATIONDATE%TYPE,
        R_CRUSER   MLER_BULK.CREATIONUSER%TYPE,
        R_CRPGM    MLER_BULK.CREATIONPROGRAM%TYPE,
        R_MDATE    MLER_BULK.MAINTENANCEDATE%TYPE,
        R_MUSER    MLER_BULK.MAINTENANCEUSER%TYPE,
        R_MPGM     MLER_BULK.MAINTENANCEPROGRAM%TYPE);
 
  TYPE MLER_BULK_TAB IS TABLE OF MLER_BULK_REC;    
       
  V_MLER_BULK      MLER_BULK_TAB;
  V_ARRAY_SIZE     NUMBER := 10000;
  V_TOT_CNT        NUMBER(20) :=0;
 
  CURSOR C1 IS
         SELECT
          BATCHNUMBER,
          LOADTRACKDETAILKEY,
          MLFINITESTATEID,
          MLFINITESTATEDESC,
          MLAPPLIESTOALLPLANS,
          MLLOADRECORDTOMEM,
          CREATIONDATE,
          CREATIONUSER,
          CREATIONPROGRAM,
          MAINTENANCEDATE,
          MAINTENANCEUSER,
          MAINTENANCEPROGRAM
         FROM mler_bulk_arch m
           WHERE m.batchnumber IN
           (SELECT h.batchnumber FROM MLHEADER_ARCH h);
           
         
  PROCEDURE writelog(pMSG IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
 
  BEGIN -- writelog
    INSERT INTO POST_BULK_MLER_LOG
           VALUES ('BULK_COLLECT_INS_MLER','MAIN',pMsg, SYSDATE, 0);
    COMMIT;
  END writelog;


  BEGIN
    writelog('Started');
 
 

  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT
          INTO
              V_MLER_BULK LIMIT V_ARRAY_SIZE;

BEGIN
         
     INSERT INTO mler_bulk
    (BATCHNUMBER,
     LOADTRACKDETAILKEY,
     MLFINITESTATEID,
     MLFINITESTATEDESC,
     MLAPPLIESTOALLPLANS,
     MLLOADRECORDTOMEM,
     CREATIONDATE,
     CREATIONUSER,
     CREATIONPROGRAM,
     MAINTENANCEDATE,
     MAINTENANCEUSER,
     MAINTENANCEPROGRAM)
     SELECT    
     BATCHNUMBER,
     LOADTRACKDETAILKEY,
     MLFINITESTATEID,
     MLFINITESTATEDESC,
     MLAPPLIESTOALLPLANS,
     MLLOADRECORDTOMEM,
     CREATIONDATE,
     CREATIONUSER,
     CREATIONPROGRAM,
     MAINTENANCEDATE,
     MAINTENANCEUSER,
     MAINTENANCEPROGRAM
     FROM mler_bulk_arch;
               
         
  EXIT WHEN V_MLER_BULK.COUNT=0;
     
  V_TOT_CNT:=V_TOT_CNT+V_MLER_BULK.COUNT;
 
  COMMIT;
 
  writelog('Inserted into MLER_BULK so far: '|| V_TOT_CNT);
 
  END LOOP;
 
  CLOSE C1;
 
  COMMIT;
 
  writelog('Finished - Total Rows: '|| V_TOT_CNT);
END LOOP;  

EXCEPTION
  WHEN OTHERS THEN
    writelog(SQLERRM);
END;

MikeOM - That compiled cleanly and executed beautifullly:

I've also attached the POST_BULK_MLER_LOG in xls format.

PROCEDURE BULK_COLLECT_INTO_MLER
IS
   CURSOR c1
   IS
      SELECT batchnumber, loadtrackdetailkey, mlfinitestateid,
             mlfinitestatedesc, mlappliestoallplans, mlloadrecordtomem,
             creationdate, creationuser, creationprogram, maintenancedate,
             maintenanceuser, maintenanceprogram
        FROM mler_bulk_arch m
       WHERE m.batchnumber IN (SELECT h.batchnumber
                                 FROM mlheader_arch h);
 
   mler_bulk_rec   c1%ROWTYPE;
 
   TYPE mler_bulk_tab IS TABLE OF c1%rowtype;
 
   v_mler_bulk     mler_bulk_tab;
   v_array_size    NUMBER        := 10000;
   v_tot_cnt       NUMBER (20)   := 0;
   i               PLS_INTEGER;
 
   PROCEDURE writelog (pmsg IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   -- Writelog
      INSERT INTO post_bulk_mler_log
           VALUES ('Bulk_Collect_Ins_Mler', 'Main', pmsg, SYSDATE, 0);
 
      COMMIT;
   END writelog;
BEGIN
   writelog ('Started');
 
   OPEN c1;
 
   LOOP
      FETCH c1
      BULK COLLECT INTO v_mler_bulk LIMIT v_array_size;
 
      EXIT WHEN v_mler_bulk.COUNT = 0;
 
      BEGIN
         FORALL i IN 1 .. v_mler_bulk.COUNT
            INSERT INTO (SELECT batchnumber, loadtrackdetailkey,
                                mlfinitestateid, mlfinitestatedesc,
                                mlappliestoallplans, mlloadrecordtomem,
                                creationdate, creationuser,
                                creationprogram,maintenancedate,
                                maintenanceuser, maintenanceprogram
                           FROM mler_bulk)
                 VALUES v_mler_bulk (i);
         v_tot_cnt := v_tot_cnt + v_mler_bulk.COUNT;
         COMMIT;
         writelog ('Inserted Into Mler_Bulk So Far: ' || v_tot_cnt);
      END;
 
      COMMIT;
   END LOOP;
 
   CLOSE c1;
 
   writelog ('Finished - Total Rows: ' || v_tot_cnt);
EXCEPTION
   WHEN OTHERS
   THEN
      writelog (SQLERRM);
END;