Implementation Restricted Relaxed in Oracle 11g

0 0

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@orcl
Connected.
SQL> select *
  2    from v$version
  3  /

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL 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 records
ORA-06550: line 19, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 19, column 15:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 18, column 7:
PL/SQL: SQL Statement ignored


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

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta
PL/SQL Release 11.1.0.4.0 - Beta
CORE    11.1.0.4.0      Beta
TNS for 32-bit Windows: Version 11.1.0.4.0 - Beta
NLSRTL 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

About Post Author

Alex Nuijten

alex.nuijten@amis.nl

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Oracle Quiz on SQL and PL/SQL – See Water Burning – paper for ODTUG 2005 Death of the End Date – How LAG and LEAD help fight redundancy PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving Oracle 11g for Developers – presenting at Oracle Benelux User Group Conference 2007 Oracle Open World / Java One Preview bij AMIS