oracleplsqlinsertbulkinsertforall

PLSQL : FORALL insert when data is NOT from the type


I want to use FORALL to insert data into a table. But, in my below code I will not be able to get l_final_amt and l_reference_number variables outside the FOR loop of l_tbl_table_test_retrieve.

How to use FORALL to insert data into a table when values are not in the given type?

CREATE OR REPLACE PACKAGE test_FORALL AS
PROCEDURE pr_test_FORALL;
END test_FORALL;

CREATE OR REPLACE PACKAGE BODY test_FORALL AS
PROCEDURE pr_test_FORALL IS

TYPE ty_tbl_table_test IS TABLE OF table_test%ROWTYPE INDEX BY BINARY_INTEGER;
l_tbl_table_test_retrieve            ty_tbl_table_test;
l_tbl_table_test                     ty_tbl_table_test;
l_final_amt                          INTEGER;
l_reference_number                   VARCHAR2(100);

BEGIN
          SELECT * BULK COLLECT
          INTO   l_tbl_table_test_retrieve
          FROM   table_test t1;

          FOR i IN 1 .. l_tbl_table_test_retrieve.COUNT
          LOOP

          l_tbl_table_test(l_tbl_table_test.COUNT + 1) := l_tbl_table_test_retrieve(i);
          l_final_amt := l_final_amt + 10;
          l_reference_number := SYSDATE + l_tbl_table_test_retrieve(i).ID;

          insert into some_other_table(fname, address,final_amt,ref_number)
          values(l_tbl_table_test_retrieve(i).fname, l_tbl_table_test_retrieve(i).address,l_final_amt,l_reference_number); 

          END LOOP;

        --I want to insert into some_other_table using FORALL. But,l_final_amt and l_reference_number variables
          -- are not available in l_tbl_table_test_retrieve.    

EXCEPTION
DBMS_OUTPUT.put_line('EXCEPTION occurred');

END;

END pr_test_FORALL;
END test_FORALL;

Solution

  • Use a cursor and add the fields into the rows returned by the cursor:

    PROCEDURE pr_test_FORALL IS
      DECLARE csrData AS CURSOR FOR
        SELECT t1.*,
               NULL AS COUNT_VAL,
               NULL AS FINAL_AMT,
               NULL AS REFERENCE_NUMBER
          FROM TABLE_TEST t1;
    
      TYPE ty_tbl_table_test IS
        TABLE OF csrData%ROWTYPE  -- Note: csrData%ROWTYPE
          INDEX BY BINARY_INTEGER;
    
      l_tbl               ty_tbl_table_test;
      l_final_amt         INTEGER := 0;
      l_reference_number  VARCHAR2(100);
    BEGIN
      OPEN csrData
    
      FETCH csrData
        BULK COLLECT INTO l_tbl;
    
      CLOSE csrData;
    
      FOR i IN 1 .. l_tbl.COUNT LOOP
        l_final_amt := l_final_amt + 10;
        l_tbl(i).FINAL_AMT := l_final_amt;
        l_tbl(i).REFERENCE_NUMBER := SYSDATE + l_tbl(i).ID;
      END LOOP;
    
      FORALL i IN l_tbl.FIRST..l_tbl.LAST
        INSERT INTO SOME_OTHER_TABLE
          (FNAME, ADDRESS, FINAL_AMT, REF_NUMBER)
        VALUES
          (l_tbl(i).FNAME,
           l_tbl(i).ADDRESS,
           l_tbl(i).FINAL_AMT,
           l_tbl(i).REFERENCE_NUMBER);
    EXCEPTION
      DBMS_OUTPUT.put_line('EXCEPTION occurred');
    END pr_test_FORALL;