Implementation Restricted Relaxed in Oracle 11g

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&gt; conn scott/tiger@orcl<br />Connected.<br />SQL&gt; select *<br />  2    from v$version<br />  3  /<br /><br />BANNER<br />----------------------------------------------------------------<br />Personal Oracle Database 10g Release 10.2.0.1.0 - Production<br />PL/SQL Release 10.2.0.1.0 - Production<br />CORE    10.2.0.1.0      Production<br />TNS for 32-bit Windows: Version 10.2.0.1.0 - Production<br />NLSRTL Version 10.2.0.1.0 - Production<br /><br />SQL&gt; declare<br />  2     type emp_rt is record<br />  3      (empno number (4)<br />  4      ,ename varchar2(25)<br />  5      ,job varchar2(25)<br />  6      );<br />  7     type emps_tt is table of emp_rt<br />  8        index by binary_integer;<br />  9     emps emps_tt;<br /> 10     i binary_integer;<br /> 11  begin<br /> 12     select empno / 2<br /> 13          , ename<br /> 14          , job<br /> 15       bulk collect into emps<br /> 16       from emp;<br /> 17     forall idx in 1..emps.count<br /> 18        insert into emp (empno)<br /> 19        values (emps(idx).empno);<br /> 20  end;<br /> 21  /<br />      values (emps(idx).empno);<br />              *<br />ERROR at line 19:<br />ORA-06550: line 19, column 15:<br />PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records<br />ORA-06550: line 19, column 15:<br />PLS-00382: expression is of wrong type<br />ORA-06550: line 19, column 15:<br />PL/SQL: ORA-22806: not an object or REF<br />ORA-06550: line 18, column 7:<br />PL/SQL: SQL Statement ignored<br /><br /><br />SQL&gt; conn scott/tiger@lab<br />Connected.<br />SQL&gt; select *<br />  2    from v$version<br />  3  /<br /><br />BANNER<br />--------------------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta<br />PL/SQL Release 11.1.0.4.0 - Beta<br />CORE    11.1.0.4.0      Beta<br />TNS for 32-bit Windows: Version 11.1.0.4.0 - Beta<br />NLSRTL Version 11.1.0.4.0 - Beta<br /><br />SQL&gt; declare<br />  2     type emp_rt is record<br />  3      (empno number (4)<br />  4      ,ename varchar2(25)<br />  5      ,job varchar2(25)<br />  6      );<br />  7     type emps_tt is table of emp_rt<br />  8        index by binary_integer;<br />  9     emps emps_tt;<br /> 10     i binary_integer;<br /> 11  begin<br /> 12     select empno / 2<br /> 13          , ename<br /> 14          , job<br /> 15       bulk collect into emps<br /> 16       from emp;<br /> 17     forall idx in 1..emps.count<br /> 18        insert into emp (empno)<br /> 19        values (emps(idx).empno);<br /> 20  end;<br /> 21  /<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL&gt; select *<br />  2    from emp<br />  3  /<br /><br />     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO<br />---------- ---------- --------- ---------- --------- ---------- ---------- ----------<br />      7369 SMITH      CLERK           7902 17-DEC-80        800                    20<br />      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30<br />      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30<br />      7566 JONES      MANAGER         7839 02-APR-81       2975                    20<br />      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30<br />      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30<br />      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10<br />      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20<br />      7839 KING       PRESIDENT            17-NOV-81       5000                    10<br />      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30<br />      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20<br />      7900 JAMES      CLERK           7698 03-DEC-81        950                    30<br />      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20<br />      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10<br />      3685<br />      3750<br />      3761<br />      3783<br />      3827<br />      3849<br />      3891<br />      3894<br />      3920<br />      3922<br />      3938<br />      3950<br />      3951<br />      3967<br /><br />28 rows selected.&nbsp;<br />&nbsp;

Isn’t that wonderful?… 

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