Bulk Collect ... FORALL...Insert

http://www.oracle-developer.net/display.php?id=410

workaround to inserts

SQL> DECLARE  
2  
3     TYPE subset_rt IS RECORD  
4     ( empno    emp.empno%TYPE 
5     , ename    emp.ename%TYPE 
6     , hiredate emp.hiredate%TYPE  
7     , deptno   emp.deptno%TYPE ); 
8  
9     TYPE subset_aat IS TABLE OF subset_rt 
10        INDEX BY PLS_INTEGER; 
11 
12     aa_subset subset_aat; 
13 
14  BEGIN 
15 
16     /* Some "source" data... */ 
17     SELECT ROWNUM, owner, created, 20 
18     BULK   COLLECT INTO aa_subset 
19     FROM   all_objects 
20     WHERE  ROWNUM <= 10; 
21 
22     /* Record-based insert and subset of columns... */ 
23     FORALL i IN 1 .. aa_subset.COUNT 
24        INSERT INTO (SELECT empno, ename, hiredate, deptno FROM emp) 
25        VALUES aa_subset(i); 
26 
27     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'); 
28 
29  END; 
30  /

10 rows inserted.  

PL/SQL procedure successfully completed. 

workaround using objects and collections
SQL> CREATE TYPE emp_ot AS OBJECT  
2  ( empno NUMBER(4)  
3  , ename VARCHAR2(10)  
4  , sal   NUMBER(7,2)  
5  );  
6  /

Type created.


SQL> CREATE TYPE emp_ntt AS TABLE OF emp_ot;  
2  /
Type created.

SQL> DECLARE  
2    
3     nt_emp emp_ntt;  
4    
5  BEGIN  
6    
7     /* Populate the demo collection... */  
8     SELECT emp_ot(empno, ename, sal) BULK COLLECT INTO nt_emp  
9     FROM   emp; 
10   
11     /* FORALL with workaround... */ 
12     FORALL i IN 1 .. nt_emp.COUNT 
13        UPDATE emp 
14        SET    ename = TREAT(nt_emp(i) AS emp_ot).ename 
15        ,      sal   = TREAT(nt_emp(i) AS emp_ot).sal * 1.1 
16        WHERE  empno = TREAT(nt_emp(i) AS emp_ot).empno; 
17   
18     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows updated.' ); 
19  
20  END; 
21  /

14 rows updated. 

PL/SQL procedure successfully completed.