tkprof shows us "what went wrong". But before we go there, lets just understand what "update where current of" is doing.

Basically, when you use an "update where current of", PLSQL is retrieving the rowid at query time and issueing an update ... where rowid = :x for you.kkkkk consider:

create table t ( x int );
create table t2 ( x int );
insert into t values ( 1 );
insert into t2 values ( 1 );


declare
cursor c is select * from t for update;
l_rec t%rowtype;
begin
open c;
fetch c into l_rec;
update t set x = 2 where current of c;
close c;
end;
********************************************************************************
SELECT * FROM T FOR UPDATE

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 7 2 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 14 2 1
********************************************************************************
UPDATE T SET X = 2 WHERE ROWID = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1 1 1



so, all that did was to automate the fetching of a rowid and then an update by rowid..

When we do two tables, as long as only one of the tables is "for updatable", PLSQL does the right thing:


declare
cursor c is select t.x tx, t2.x t2x from t, t2 for update of t.x;
l_rec c%rowtype;
begin
open c;
fetch c into l_rec;
update t set x = 2 where current of c;
close c;
end;
********************************************************************************
SELECT T.X TX, T2.X T2X FROM T, T2 FOR UPDATE OF T.X

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 14 2 0
Fetch 1 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 28 2 1
********************************************************************************
UPDATE T SET X = 2 WHERE ROWID = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 1 1


it updated the single row in T, however, when it becomes ambigous as to which table would be modified:

declare
cursor c is select t.x tx, t2.x t2x from t, t2 for update of t.x, t2.x;
l_rec c%rowtype;
begin
open c;
fetch c into l_rec;
update t set x = 2 where current of c;
close c;
end;
********************************************************************************
SELECT T.X TX, T2.X T2X FROM T, T2 FOR UPDATE OF T.X, T2.X

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 14 3 0
Fetch 1 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 28 3 1
********************************************************************************
UPDATE T SET X = 2 WHERE ROWID = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0


no rows updated -- the rowid isn't "known"

While you may reference multiple tables in a FOR UPDATE, only one may be in the for update clause if you wish to use where current of.

I personally have never used where current of, the technique I always use is as follows:

ops$tkyte@ORA9IR2> declare
2 cursor mycur is
3 select t1.myvalue v1, t1.rowid t1rid,
4 t2.myvalue v2, t2.rowid t2rid
5 from t_dave1 t1, t_dave2 t2
6 where t1.mykey = t2.mykey
7 for update of t1.myvalue, t2.myvalue;
8 begin
9 for myrec in mycur loop
10 dbms_output.put_line(myrec.v1||','||myrec.v2);
11 update t_dave1
12 set myvalue = 'After Update'
13 where rowid = myrec.t1rid;
14 update t_dave2
15 set myvalue = 'After Update'
16 where rowid = myrec.t2rid;
17 end loop;
18 for myrec in mycur loop
19 dbms_output.put_line(myrec.v1||','||myrec.v2);
20 end loop;
21 end;
22 /
Before Update,Before Update
After Update,After Update

PL/SQL procedure successfully completed.