Implementation Restricted Relaxed in Oracle 11g

2
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

2 Comments