Hello! We are running Oracle 9i here. I am using Toad for Oracle to create PL/SQL. I create a select for update cursor in order to lock a single row of
data in a table. I use the table key to insure only one record gets locked. Later in my code I do an UPDATE mytable SET ... WHERE CURRENT
OF mycursor. This works fine except I want to do an UPDATE mytable SET ... WHERE CURRENT OF mycursor RETURNING afield INTO myvariable. For some reason this won't compile -- I get an ORA-00933. Can I do a CURRENT OF with a RETURNING clause?
Requires Free Membership to View
This is a bug in Oracle 9i release 1 (9.0.1)
Workaround:
Use ROWID instead of WHERE CURRENT OF as in
declare
cursor c1 is
select ename, ROWID
from emp
for update of sal;
l_rowid ROWID;
l_ename VARCHAR2(30);
l_empno NUMBER;
begin
open c1;
fetch c1 INTO l_ename,l_rowid;
if c1%found THEN
update emp
set sal = 666
where rowid=l_rowid returning empno INTO l_empno;
end if;
CLOSE c1;
end;
This was first published in November 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation