Question on using cursors 1

[mahesh736]

Hi,

I have defined a cursor by joining 2 tables.
Can I use "CURRENT OF" to update values in BOTH the tables which are joined in the cursor definition?

eg. declare
cursor my_cur is
select t1.col_1, t1.col_2, t2.col_1, t2.col_22
from t1, t2
where t1.col_1 = t2.col_1
for UPDATE OF t1.col_3, t2.col_3;

open my_cur;
loop
fetch my_cur into my_rec;
...
...
update t1 set col_3 = 'Y' where CURRENT OF my_cur; --(i)
update t2 set col_3 = 'Y' where CURRENT OF my_cur; --(ii)

end loop;

So is it syntactically correct to UPDATE the joined tables
t1 and t2 as in (i) and (ii) above.

Thanks

 

[sem]

No, because WHERE CURRENT OF stands for WHERE ROWID= and in the case of multiple tables you can not specify which rowid you're using, thus the condition will be false and no rows will be updated.

 

[mahesh736]

What is the best option to resolve this problem?
I was thinking of another option; selecting the ROWID's in the cursor itself.

ie
declare
cursor my_cur is
select t1.ROWID RowID1, t2.ROWID RowID2, t1.col_1, t1.col_2, t2.col_1, t2.col_22 from t1, t2
where t1.col_1 = t2.col_1
for UPDATE OF t1.col_3, t2.col_3;
...
...

update t1 set col_3 = 'Y' where Rowid = RowID1; --(i)
update t2 set col_3 = 'Y' where RowId = RowID2; --(ii)


Will this correctly update? Any other suggestions.

Thanks for your help
Mahesh.