If you are a regular user of the FORALL
statement, you are probably also familiar with this message:   

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

While I was playing around on our Oracle 11g database, I found that this restriction was lifted. How cool is that?

....

Here is a short demonstration of code that fails on an Oracle 10gR2, but succeeds on an Oracle 11g.

 

SQL> conn scott/tiger@orclConnected.SQL> select *  2    from v$version  3  /

BANNER----------------------------------------------------------------Personal Oracle Database 10g Release 10.2.0.1.0 - ProductionPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production

SQL> declare  2     type emp_rt is record  3      (empno number (4)  4      ,ename varchar2(25)  5      ,job varchar2(25)  6      );  7     type emps_tt is table of emp_rt  8        index by binary_integer;  9     emps emps_tt; 10     i binary_integer; 11  begin 12     select empno / 2 13          , ename 14          , job 15       bulk collect into emps 16       from emp; 17     forall idx in 1..emps.count 18        insert into emp (empno) 19        values (emps(idx).empno); 20  end; 21  /      values (emps(idx).empno);              *ERROR at line 19:ORA-06550: line 19, column 15:PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of recordsORA-06550: line 19, column 15:PLS-00382: expression is of wrong typeORA-06550: line 19, column 15:PL/SQL: ORA-22806: not an object or REFORA-06550: line 18, column 7:PL/SQL: SQL Statement ignored

SQL> conn scott/tiger@labConnected.SQL> select *  2    from v$version  3  /

BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - BetaPL/SQL Release 11.1.0.4.0 - BetaCORE    11.1.0.4.0      BetaTNS for 32-bit Windows: Version 11.1.0.4.0 - BetaNLSRTL Version 11.1.0.4.0 - Beta

SQL> declare  2     type emp_rt is record  3      (empno number (4)  4      ,ename varchar2(25)  5      ,job varchar2(25)  6      );  7     type emps_tt is table of emp_rt  8        index by binary_integer;  9     emps emps_tt; 10     i binary_integer; 11  begin 12     select empno / 2 13          , ename 14          , job 15       bulk collect into emps 16       from emp; 17     forall idx in 1..emps.count 18        insert into emp (empno) 19        values (emps(idx).empno); 20  end; 21  /

PL/SQL procedure successfully completed.

SQL> select *  2    from emp  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10      3685      3750      3761      3783      3827      3849      3891      3894      3920      3922      3938      3950      3951      3967

28 rows selected.  

Isn’t that wonderful?… 

Until you move to Oracle 11g, here is a workaround for this exception: avoiding pls-00436 with forall