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?
This is a bug in Oracle 9i release 1 (9.0.1)
Use ROWID instead of WHERE CURRENT OF as in
cursor c1 is
select ename, ROWID
for update of sal;
fetch c1 INTO l_ename,l_rowid;
if c1%found THEN
set sal = 666
where rowid=l_rowid returning empno INTO l_empno;
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.