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 /