Unable to load the data into a nested table(collection)

Question

I would like to load data into a collection from various cursors. and below you can see a small example of the same. To simply put, I need to take columns from various tables and load it into a single table. Initially we were using a direct insert statement which was working fine, however it is causing issues recently. So we are trying to introduce a collection which would collect the data and from there we will load the data to the table.

    declare
    vin date;
    cursor c1 is select emp_id.. from emp;
    cursor c2 is select dept_id , ... from dept;
    type t3 is table of t%rowtype;
    t1 t3 := t3();
    begin
    for i in (select emp_id,dept_id,vin from dual)
    loop
    t1.extend;
    t1(t1.count).load_date := i.vin;
    t1(t1.count).emp_id := i.emp_id;
     t1(t1.count).dept_no := i.dept_id;
    end loop;
    forall  j in 1..t1.last
     insert into t values (t1(j).emp_id,t1(j).dept_id,t1(j).load_date);
     end;

When i tried to run this one, I'm getting an error stating PLS00308 :that constructor is not allowed as the origin of the assignment. Kindly help me on this.

Answer 1

Don't use PL/SQL, cursors or collection. Just use INSERT INTO ... SELECT ... and JOIN the tables:

INSERT INTO t (emp_id, dept_id, load_date)
SELECT e.emp_id,
       d.dept_id,
       SYSDATE -- or the column where you stored the date
FROM   emp e
       INNER JOIN dept d
       ON (e.dept_id = d.dept_id) -- or however you want to join the tables.

Answer 2

Try it like below:

SET SERVEROUTPUT ON
Declare
    TYPE type_a_tbl is Table Of A_TBL%ROWTYPE INDEX BY BINARY_INTEGER;
    i   BINARY_INTEGER := 0;
    tab_a_tbl  type_a_tbl;
    --
    CURSOR c IS
        Select      e.EMPNO, e.ENAME, d.DEPTNO, d.DNAME, d.LOC, e.MGR
        From        EMP e
        Inner Join  DEPT d ON(d.DEPTNO = e.DEPTNO)
        Where       d.DEPTNO = 30;
    cSet    c%ROWTYPE;
    --
Begin
    OPEN c;
    LOOP
        FETCH c Into cSet;
        EXIT WHEN c%NOTFOUND;
        i := i + 1;
        tab_a_tbl(i).EMPNO := cSet.EMPNO; 
        tab_a_tbl(i).ENAME := cSet.ENAME;
        tab_a_tbl(i).DEPTNO := cSet.DEPTNO;
        tab_a_tbl(i).DNAME := cSet.DNAME;
        tab_a_tbl(i).LOC := cSet.LOC;
        tab_a_tbl(i).MGR := cSet.MGR;
        --
        Insert Into A_TBL VALUES(tab_a_tbl(i).EMPNO, 
                                 tab_a_tbl(i).ENAME, 
                                 tab_a_tbl(i).DEPTNO, 
                                 tab_a_tbl(i).DNAME,
                                 tab_a_tbl(i).LOC, 
                                 tab_a_tbl(i).MGR);
                                 
        dbms_output.put_line('Row inserted: --> ' || tab_a_tbl(i).EMPNO || Chr(9) || Chr(9) || 
                                                     LPAD(tab_a_tbl(i).ENAME, 8, ' ') || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).DEPTNO || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).DNAME || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).LOC || Chr(9) || Chr(9) || 
                                                     tab_a_tbl(i).MGR);
        --
    END LOOP;
    CLOSE c;
End;
/
/*
anonymous block completed
Row inserted: --> 7499         ALLEN        30      SALES       CHICAGO     7698
Row inserted: --> 7521          WARD        30      SALES       CHICAGO     7698
Row inserted: --> 7654        MARTIN        30      SALES       CHICAGO     7698
Row inserted: --> 7698         BLAKE        30      SALES       CHICAGO     7839
Row inserted: --> 7844        TURNER        30      SALES       CHICAGO     7698
Row inserted: --> 7900         JAMES        30      SALES       CHICAGO     7698
*/

... or with two cursors (result is the same)

SET SERVEROUTPUT ON
Declare
    TYPE type_a_tbl is Table Of A_TBL%ROWTYPE INDEX BY BINARY_INTEGER;
    i   BINARY_INTEGER := 0;
    tab_a_tbl  type_a_tbl;
    --
    CURSOR depts IS
        Select d.DEPTNO, d.DNAME, d.LOC From DEPT d Where DEPTNO = 30;
    deptSet     depts%ROWTYPE;
        --
        CURSOR c IS
            Select      e.EMPNO, e.ENAME, e.MGR
            From        EMP e
            Where       e.DEPTNO = deptSet.DEPTNO;
        cSet    c%ROWTYPE;
        --
Begin
    OPEN depts;
    LOOP
        FETCH depts Into deptSet;
        EXIT WHEN depts%NOTFOUND;
        --
            OPEN c;
            LOOP
                FETCH c Into cSet;
                EXIT WHEN c%NOTFOUND;
                i := i + 1;
                tab_a_tbl(i).EMPNO := cSet.EMPNO; 
                tab_a_tbl(i).ENAME := cSet.ENAME;
                tab_a_tbl(i).DEPTNO := deptSet.DEPTNO;
                tab_a_tbl(i).DNAME := deptSet.DNAME;
                tab_a_tbl(i).LOC := deptSet.LOC;
                tab_a_tbl(i).MGR := cSet.MGR;
                --
                Insert Into A_TBL VALUES(tab_a_tbl(i).EMPNO, 
                                         tab_a_tbl(i).ENAME, 
                                         tab_a_tbl(i).DEPTNO, 
                                         tab_a_tbl(i).DNAME,
                                         tab_a_tbl(i).LOC, 
                                         tab_a_tbl(i).MGR);
                                         
                dbms_output.put_line('Row inserted: --> ' || tab_a_tbl(i).EMPNO || Chr(9) || Chr(9) || 
                                                             LPAD(tab_a_tbl(i).ENAME, 8, ' ') || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).DEPTNO || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).DNAME || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).LOC || Chr(9) || Chr(9) || 
                                                             tab_a_tbl(i).MGR);
                --
            END LOOP;
            CLOSE c;
        --
    END LOOP;
    CLOSE depts;
End;
/