Q
Problem solve Get help with specific problems with your technologies, process and projects.

CURRENT OF with a RETURNING clause

I am using Toad for Oracle to create PL/SQL. I get an ORA-00933. Can I do a CURRENT OF with a RETURNING clause?

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)

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 last published in November 2006

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close