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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close