PLS-00436 Restriction in FORALL Statements Removed From 11g

The PLS-00436 restriction has been removed from 11g, and now we can reference each element of the collection object when used with FORALL statement.Each element can now be referenced using SET and WHERE clauses of a DML statement in a FORALL construct.

Sample program :

conn scott/tiger
create table demo_plsql_436 as select * from emp where 1=2;

/* The procedure "display_demo_plsql_436" will display the data of  table demo_plsql_436 */

create or replace procedure display_demo_plsql_436 as
   cursor c1 is select ename, sal from demo_plsql_436;
   emp_name demo_plsql_436.ename%type;
   emp_sal  demo_plsql_436.sal%type;
begin
   open c1;
   loop
      fetch c1 into emp_name, emp_sal;
   exit when c1%notfound;
   dbms_output.put_line(rpad (emp_name,10,' ')|| '     ' || rpad(emp_sal,7,' '));
   end loop;
   close c1;
end display_demo_plsql_436;
/

/* The below given sample code will demonstrate the enhancement of removing the  restriction PLS-00436 while using FORALL  with DML*/

set serveroutput on
declare
  type empcol_t is table of emp%rowtype index by pls_integer;
  empcol empcol_t;
begin
  select * bulk collect into empcol from emp;

/*Case #1 :Inserting the data referencing individual elements for the collection using FORALL*/

  forall i in empcol.first..4
    insert into demo_plsql_436 (empno, ename, sal)
      values (empcol(i).empno, empcol(i).ename, empcol(i).sal);
   dbms_output.put_line('=====Data Inserted without error==========');
     display_demo_plsql_436;
 
/*Case #2 : Update rows where some key value (empno in this case) matches the key value in the record bind into the bulk DML operation.*/

  for i in empcol.first..4 loop
    empcol(i).sal := empcol(i).sal * 1.1;
  end loop;
  dbms_output.put_line('Salary updated in Nested table');
 
forall i in empcol.first..4
    update demo_plsql_436 set row = empcol(i)
      where empno = empcol(i).empno;
dbms_output.put_line('=====Salary updated without error==========');
     display_demo_plsql_436;
 
/*Case #3 : Update a subset of columns in the SET clause with values from the record in bind into the bulk DML operation*/
  
  for i in empcol.first..4 loop
    empcol(i).sal := empcol(i).sal * 1.5;
  end loop;
 
forall i in empcol.first..4
    update demo_plsql_436 set (sal) = empcol(i).sal
      where empno = empcol(i).empno;
dbms_output.put_line('=====Revised salary updated without error==========');
     display_demo_plsql_436;
end;
/
Sample Output:

=====Data Inserted without error==========
SMITH          800
ALLEN          1600
WARD           1250
JONES          2975
Salary updated in Nested table
=====Salary updated without error==========
SMITH          880
ALLEN          1760
WARD           1375
JONES          3272.5
=====Revised salary updated without error==========
SMITH          1320
ALLEN          2640
WARD           2062.5
JONES          4908.75

The Restriction still hold good when referencing each element of the collect with returning clause.But now from 11g onwards we get a new error.

PLS-437: FORALL bulk index cannot be used in RETURNING clause.

Sample Code to demonstrate the restriction :

SQL> declare
  2    type empcol_t is table of emp%rowtype index by pls_integer;
  3    empcol empcol_t;
  4  begin
  5    select * bulk collect into empcol from emp;
  6
  7    forall i in empcol.first..empcol.last
  8      update emp set row = empcol(i)
  9        where empno = empcol(i).empno
 10        returning sal bulk collect into empcol(i).sal;
 11
 12  end;
 13  /
      returning sal bulk collect into empcol(i).sal;
                                      *
ERROR at line 10:
ORA-06550: line 10, column 39:
PLS-00437: FORALL bulk index cannot be used in RETURNING clause
ORA-06550: line 10, column 52:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

Error Message in 10g

ERROR at line 9:
ORA-06550: line 9, column 21:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
ORA-06550: line 9, column 21:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 39:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
ORA-06550: line 10, column 52:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored