Skip to Main Content
  • Questions
  • Can we use RETURNING CLAUSE along with CURRENT OF clause in update statement.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nidhi.

Asked: August 05, 2020 - 1:23 pm UTC

Answered by: Chris Saxon - Last updated: August 06, 2020 - 10:15 am UTC

Category: PL/SQL - Version: PL/SQL Release 11.2.0.4.0

Viewed 100+ times

You Asked

Hi Tom,

I am using below update statement in my procedure to return few columns that are getting updated in the update statement.

UPDATE DUMMY_TABLE SET DUMMY_STATUS = 'ABC'
WHERE CURRENT OF DUMMY_CURSOR 
RETURNING DUMMY_FIELD1, DUMMY_FIELD2
BULK COLLECT INTO
TAB_FIELD1,
TAB_FIELD2;


The above code works if i am not using CURRENT OF CLAUSE, but is giving error when used like above...
I want to know if i am making any syntax error here...or this is not possible at all..
Note: I cannot remove current of clause from the update statement, and still I have to return the columns that are getting updated.


Thanks

and we said...

Sadly no. The returning clause doesn't work with where current of.

In many cases you can work around this by assigning the new value to a variable. Then using that in your update:

declare
  cursor cur is
    select * from t
    for update;
  rec cur%rowtype;
  new_val date;
begin
  open cur;
  loop
    fetch cur into rec;
    exit when cur%notfound;
    
    new_val := sysdate;
    
    update t
    set    c2 = new_val
    where  current of cur;
    
  end loop;
  close cur;
end;
/


You may also be better off switching to bulk processing. Which does support returning:

declare
  cursor cur is
    select * from t
    for update;
  type cur_tab is
    table of cur%rowtype
    index by pls_integer;

  recs cur_tab;
  dates dbms_sql.date_table;
begin
  open cur;
  loop
    fetch cur bulk collect into recs limit 100;
    exit when recs.count = 0;
    
    forall i in 1 .. recs.count     
      update t
      set    c2 = sysdate
      where  c1 = recs (i).c1
      returning c2
      bulk collect into dates;
    
  end loop;
  close cur;
end;
/

and you rated our response

  (1 rating)

Reviews

August 07, 2020 - 5:12 am UTC

Reviewer: Nidhi

Thanks for your quick response Chris.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.