How to insert into a table correctly using table of records and forall in pl/sql

Question

I want to insert records into MY_TABLE using forall. But the no. of records dat gets inserted keeps on changing with each test run! I think it has something to do with loop counter but I am not able to figure out. Here's the code snippet.

DECLARE
  TYPE l_rec_type IS RECORD (
        datakey SOURCE_TABLE.datakey%TYPE,
        sourcekey SOURCE_TABLE.sourcekey%TYPE,
        DESCRIPTION SOURCE_TABLE.DESCRIPTION%TYPE,
        dimension_name SOURCE_TABLE.dimension_name%TYPE ,
        data_type   SOURCE_TABLE.data_type%TYPE

   );


  TYPE l_table_type IS TABLE OF l_rec_typeINDEX BY PLS_INTEGER;
  l_table l_table_type;
  l_cntr NUMBER;
BEGIN
  FOR rec_dimname IN (SELECT dimension_name FROM dimension_table) LOOP
    l_cntr1 := 1
    FOR rec_source IN (SELECT * FROM source_table WHERE data_type IS NOT NULL) LOOP
      l_table(l_ctr1).datakey := rec_source.datakey;
      l_table(l_ctr1).sourcekey := rec_source.sourcekey;
      l_table(l_ctr1).DESCRIPTION := rec_source.DESCRIPTION;
      l_table(l_ctr1).dimension_name := rec_source.dimension_name;
      l_table(l_ctr1).data_type := rec_source.data_type;
      l_cntr1 := l_cntr1+1;
    END LOOP
    FORALL j IN l_table.FIRST..l_table.LAST
      INSERT INTO my_table VALUES(l_table(j).datakey,
                                  l_table(j).sourcekey,
                                  l_table(j).DESCRIPTION,
                                  l_table(j).dimension_name,
                                  l_table(j).data_type,
                                  1,
                                  SYSDATE,
                                  login_id                            
                                 );

  END LOOP;
END;

What am I doing wrong? Normal insert using for loop is inserting 5000 records. Another problem that I am facing is how to handle WHEN DUP_VAL_ON_INDEX and WHEN OTHERS exception using forall. In nornal for loop its easy. But I have to use FORALL for fast inserts. Please help!

Answer 1

Looking at your code I can see that you not delete the data stored in the pl/table inside your loop and you don't have a order by to your query's. So if the first iteration have more data then the second you will have duplicate data.

So after initializing your l_cntr1 var (l_cntr1 := 1) you must clear your pl/table:

l_table.delete;

Hope that helps.