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!

CLEAR SCREEN
CONNECT Sys/Sys AS SYSDBA
declare
  procedure Create_User(Who in varchar2) is
    User_Does_Not_Exist exception;
    pragma Exception_Init(User_Does_Not_Exist, -01918);
  begin
    begin
      execute immediate 'drop user Usr cascade';
    exception when User_Does_Not_Exist then null; end;
    execute immediate '
      grant
        Unlimited Tablespace,
        Create Session,
        Create Table,
        Create Trigger
      to Usr identified by p';
  end Create_User; 
begin
  Create_User('Usr');
end;
/
CONNECT Usr/p
create table t(ID integer primary key, c1 varchar2(10), c2 number)
/
begin
  insert into t values(1, 'a', 10);
  insert into t values(2, 'b', 10);
  insert into t values(3, 'c', 30);
  commit;
end;
/
select ID, c1, c2 from t
/
declare
  type x is table of number;
  v x;
begin
  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);
end;
/
select ID, c1, c2 from t
/