Implementation Restricted Relaxed in Oracle 11g

Alex Nuijten 2
0 0
Read Time:3 Minute, 1 Second

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

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “Implementation Restricted Relaxed in Oracle 11g

Comments are closed.

Next Post

Package java classes (WSIF calls) in BPEL suitcase

Introduction Is it possible to package java classes which are invoked through WSIF along with the BPEL Suitcase generated by bpelc? I’ve noticed that when generating and deploying the suitcase using JDeveloper (10.1.3.x) – the class files are compiled to the output directory and packaged in the suitcase, but when […]
%d bloggers like this: