CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
Jacob
Jacob

Reputation: 14731

FORALL INSERT Not Inserting Rows

I have the following code snippet to insert rows into t1 table, however when I execute the procedure no rows are getting populated in t1 table.

CREATE OR REPLACE PROCEDURE my_proc
IS
   TYPE rt_t1 IS TABLE OF t1%ROWTYPE
                    INDEX BY BINARY_INTEGER;

   vrt_t1        rt_t1;

   TYPE t_emp_no_list IS TABLE OF t1.emp_no%TYPE
                            INDEX BY BINARY_INTEGER;

   TYPE t_emp_name_list IS TABLE OF t1.emp_name%TYPE
                              INDEX BY BINARY_INTEGER;

   TYPE t_loc_name_list IS TABLE OF t1.loc_name%TYPE
                              INDEX BY BINARY_INTEGER;

   TYPE t_hire_date_list IS TABLE OF t1.hire_date%TYPE
                               INDEX BY BINARY_INTEGER;


   l_emp_no      t_emp_no_list;
   l_emp_name    t_emp_name_list;
   l_loc_name    t_loc_name_list;
   l_hire_date   t_hire_date_list;
BEGIN
   SELECT empno,
          ename,
          loc,
          hiredate
     BULK COLLECT INTO l_emp_no,
          l_emp_name,
          l_loc_name,
          l_hire_date
     FROM (SELECT empno,
                  ename,
                  dept.loc,
                  emp.hiredate
             FROM emp JOIN dept ON emp.deptno = dept.deptno);

   FORALL i IN vrt_t1.FIRST .. vrt_t1.LAST
      INSERT INTO t1 (emp_no,
                      emp_name,
                      loc_name,
                      hire_date)
           VALUES (l_emp_no (i),
                   l_emp_name (i),
                   l_loc_name (i),
                   l_hire_date (i));


   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

Upvotes: 1

Views: 818

Answers (1)

XING
XING

Reputation: 9886

You should use cursor to achieve your requirement. See below:

CREATE OR REPLACE PROCEDURE my_proc
IS
   TYPE rt_t1 IS TABLE OF t1%ROWTYPE
      INDEX BY BINARY_INTEGER;

   vrt_t1        rt_t1;   

   cursor cur is 
   SELECT empno,
                  ename,
                  dept.loc,
                  emp.hiredate
             FROM emp JOIN dept ON emp.deptno = dept.deptno;


BEGIN

   OPEN cur;
   fetch cur BULK COLLECT INTO vrt_t1;   
   close cur;

   FORALL i IN 1 .. vrt_t1.count
      INSERT INTO t1 
           VALUES vrt_t1(i);


   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

PS: the above code will work if the number of columns of both the tables are same;

EDIT..In case you are inserting specific rows to a table then you need to create a RECORD to do so. See below example. Your code is failing coz you are trying to inserting multiple collection in one go. In your case first colelction would be an insert and others should be an update.

 CREATE TABLE t1
(
   emp_no      NUMBER,
   emp_name    VARCHAR2 (30),
   loc_name    VARCHAR2 (30),
   hire_date   DATE
);

--------------


SQL> select * from t1;

     no rows selected

CREATE OR REPLACE PROCEDURE my_proc
IS

   TYPE TBL IS RECORD
   (
     emp_no  number,
     emp_name varchar2(100),
     loc_name varchar2(100),
     hire_date date
   );

   TYPE t_emp IS TABLE OF TBL INDEX BY PLS_INTEGER;

   var_emp_det  t_emp; 


BEGIN
   SELECT ENO,
          ENAME,
          JOB,
          HIREDATE           
     BULK COLLECT INTO var_emp_det
   from emp_sal;  

   FORALL i IN 1..var_emp_det.count
      INSERT INTO t1 (emp_no,
                     emp_name,
                     loc_name,
                     hire_date)
         values  (var_emp_det(i).emp_no,
                  var_emp_det(i).emp_name,
                  var_emp_det(i).loc_name,
                  var_emp_det(i).hire_date);         

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

Output:

SQL> execute my_proc;

PL/SQL procedure successfully completed.

SQL> select * from t1;

    EMP_NO EMP_NAME                       LOC_NAME
---------- ------------------------------ ------------------------------
HIRE_DATE
---------
         2 Thomas                         IT
03-JAN-14

Upvotes: 2

Related Questions