10 August 2011

FORALL, UPDATE and Reality-Doc Conflicts (6431)

The 9 August quiz tested your knowledge of the restriction (prior to Oracle 11g) that you cannot reference fields of collections of records in a FORALL statement. The FORALL statement we showed in the question is this:
FORALL i IN 1 .. l_tab.COUNT
      UPDATE plch_employees
         SET salary = l_tab (i).salary * 1.1
       WHERE employee_id = l_tab (i).employee_id;
Notice that the same collection (l_tab) is referenced in both the SET and WHERE clauses.

A player who pays close attention to the Oracle documentation wrote as follows:

For both 11.1 and 11.2, the "PL/SQL Language Reference" documentations (as PDF, for 11.1 page 13-59, for 11.2 page 13-79) states the following: "If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection."

Well, I must admit, this was news to me. I'd never noticed that restriction and, it turns out, for a fairly good reason: neither the PL/SQL compiler nor run-time engine complains about this scenario at all.

So is the documentation correct or is the functionality of PL/SQL correct? I asked Bryn Llewellyn, PL/SQL Product Manager. Here is his reply:

"I agree that it [my demonstration code] does seem to be at odds with the claim from the docs that you quote. 'll have to ask about this and get back to you."

Bryn also provided a script (see below) that he is using to reproduce the issue.

Needless to say, I do not plan to change the scoring of the question!
  procedure Create_User(Who in varchar2) is
    User_Does_Not_Exist exception;
    pragma Exception_Init(User_Does_Not_Exist, -01918);
      execute immediate 'drop user Usr cascade';
    exception when User_Does_Not_Exist then null; end;
    execute immediate '
        Unlimited Tablespace,
        Create Session,
        Create Table,
        Create Trigger
      to Usr identified by p';
  end Create_User; 
create table t(ID integer primary key, c1 varchar2(10), c2 number)
  insert into t values(1, 'a', 10);
  insert into t values(2, 'b', 10);
  insert into t values(3, 'c', 30);
select ID, c1, c2 from t
  type x is table of number;
  v x;
  select c2
  bulk collect into v
  from t
  for update;

  forall i in 1..v.Count()
  update t
  set c2 = v(i)*2
  where c2 = v(i);
select ID, c1, c2 from t


  1. So was that script supposed to produce an error?
    Worked on for me on

  2. The script is, according to the Oracle doc, supposed to not work (I assume that's what "cannot" would imply in documentation). Instead, it works just fine.

  3. I usually don't complain about Reality-Doc Conflicts, because the usual answer is "but we learned something new" ;-)

    That's true, but what about using this feature in production? How far can I trust it to work? And when I get an error, what will Oracle support tell me: "Sorry, but didn't you read the docs, it is supposed not to work"?


  4. Once again, players of the PL/SQL Challenge improve the PL/SQL doc set!

    Bryn Llewellyn followed up with me regarding this issue:

    You found a doc bug. Thanks for bringing it to my attention. I just filed bug 12861602 and set its Publish flag to Yes. You ought to be able to read it and to track its progress. Here's a copy of my account:

    Find the account of the FORALL Statement in the 11.2 PL/SQL Language Reference in the Chapter "PL/SQL Language Elements". Then find the section "Restrictions on dml_statement". It starts thus:

    If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection. The workaround is to make a copy of the collection, and reference the original collection in the SET clause and the copy in the WHERE clause.

    This is quite simply wrong. There has never been such a restriction.

    I did some archeology and found that the account of this non-restriction was introduced in the 9.2 version of this book. Then it was worded thus:

    Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.

    Earlier versions of the book (FORALL was new in 8.1) make no mention of this non-restriction.

    The wording was changed (without, it seems, checking the behavior), in the 11.2 version of this book.