any limit on use of Returning clause?

[achtman]

Hi everyone,
Below is my PL/SQL block that updates a col using a cursor that has <FOR UPDATE clause> but can't figure out why it won't allow use of <RETURNING clause> in combination with <WHERE CURRENT OF>. My declared cursor is acct_cursor.
Table gets updated correctly when line 'RETURNING..' is commented. Reason for this line is to get the 'new data' and load into a table (index by table) for later reporting. Am I missing something here or is this code really not allowed? Appreciate any help thanks.

UPDATE account
SET a_rate = new_rate
WHERE CURRENT OF acct_cursor 
RETURNING a_rate INTO upd_rate;


RETURNING a_rate INTO upd_rate;
*
ERROR at line 84:
ORA-06550: line 84, column 8:
PL/SQL: ORA-00933: SQL command not properly ended

 

[markrem]

You can use the RETURNING clause with INSERT, UPDATE, and DELETE provided they do not involve queries. For example, the following would be illegal:

insert into emp_tmp select * from emp where id=12
returning emp_name into :myvariable;

I suspect that because you are using a refcursor and keyword CURRENT OF you're hitting this Oracle limitation.

There are some enhancements to the RETURNING clause in 10g.


MarkRem
Author, Oracle Database 10g: From Nuts to Soup

http://www.remidata.com/book_nuts2soup.htm

 

[achtman]

Thanks for the info markrem, will revisit 10g and let's see what comes out