Written by: StevenFeuersteinTW
Wednesday, August 22, 2007 7:41 AM 

Kudos to Alex Nuijten for bringing this gem to light, he wrote in his latest AMIS blog entry that:

"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?"

You are right, Alex, that is very cool. When I read it, I thought to myself, "very cool, but is it supported? Is it really going to be in the final, production version of Oracle11g?" 

Who better to know than Bryn Llewellyn, Oracle PL/SQL Product Manager? So I asked him and here is his reply:

Regarding 

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records or objects
Cause: table(bulk_index).field is not supported at run-time.
Action: Use FOR loop with instead of the FORALL DML statement (MERGE/INSERT/DELETE/UPDATE).

We have made improvements in this area in response to ER #5186976. Sadly, the documentation was not edited to reflect this, so you'll still find this:

<<
You cannot refer to individual record fields within DML statements invoked by a FORALL statement. Instead, you can specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
>>

in the 11. PL/SQL Language Reference in Chapter13. PL/SQL Language Elements, in the section titled "FORALL Statement". I just filed doc bug #6355690 to complain about this.
______________________________________________________________________

The following illustrates the changes.

Create a user with tables Emp (CREATE TABLE AS SELECT from Scott.Emp) and Newemp (CREATE TABLE AS SELECT from Emp where 0=1). Use a block with this shape:

declare
  type empcol_t is table of emp%rowtype index by pls_integer;
  empcol empcol_t;
begin
  select * bulk collect into empcol from emp
    where hiredate < to_date('01-JAN-1982', 'DD-MON-YYYY');

  for i in empcol.first..empcol.last loop
    empcol(i).sal := empcol(i).sal * 1.1;
  end loop;

  < test >
end;

Use Case #1: Update rows where some key value (empno in this case) matches the key value in the record bind into the bulk DML operation.

  forall i in empcol.first..empcol.last
    update emp set row = empcol(i)
      where empno = empcol(i).empno;

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

Use Case #3: Update a subset of columns in the SET clause with values from the record in bind into the bulk DML operation.

  forall i in empcol.first..empcol.last
    update emp set (sal) = empcol(i).sal
      where empno = empcol(i).empno;

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

Use Case #4: Variant on Use Case #3, but with insert statement.

  forall i in empcol.first..empcol.last
    insert into newemp (empno, ename, sal)
      values (empcol(i).empno, empcol(i).ename, empcol(i).sal);

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

NOTE: Use Case #2 still fails to compile. Allow attribute reference in a returning clause.

  forall i in empcol.first..empcol.last
    update emp set row = empcol(i)
      where empno = empcol(i).empno
      returning sal bulk collect into empcol(i).sal;

This failed to compile in 10.2 with PLS-00436; it fails to compile in 11.1 with this error:

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

Bryn concludes with this exciting news:

You can regard this as the official reply from Oracle's PL/SQL Product Manager: the improvements brought by ER #5186976 are supported from 11.1.0.6 onwards. 

So there you have it, folks! You can reference fields of records in collections that are bound into the DML statement of a FORALL statement – in Oracle11g.

Trackback Print

Tags:

Categories:

Location: Blogs Steven Feuerstein's Blog