You Asked

I have a refcursor for select query and return some rows . similarly i need to update the fetched rows as flagged .
How do i do it in a single procedure?

and Chris said...

There are a few ways.

If this is a named cursor with SELECT ... FOR UPDATE, you can use the WHERE CURRENT OF syntax to update the last row fetched:

create table t (
  c1 int, c2 int
);

insert into t values ( 1, 0 );
insert into t values ( 2, 0 );
insert into t values ( 3, 0 );
commit;

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

select * from t;

        C1         C2
---------- ----------
         1         42
         2         42
         3         42

This processes the rows one at a time though. If you're fetching more than a couple of rows, this will be slow.

It's better to bulk collect the rows and update them all in one pass. Here's an example of with a sys_refcursor:

declare
  cur sys_refcursor;
  rec dbms_sql.number_table;
begin
  open cur for 
    select c1 from t
    for update;
  loop
    fetch cur bulk collect into rec
    limit 100;
    exit when rec.count = 0;
    
    forall i in 1 .. rec.count 
      update t
      set    c2 = 99
      where  c1 = rec(i);
  end loop;
end;
/

select * from t;

        C1         C2
---------- ----------
         1         99
         2         99
         3         99

A big question here is why you want to do this. If this is to ensure only once processing of data, you may be better off using a queue:

https://docs.oracle.com/en/database/oracle/oracle-database/21/adque/aq-introduction.html

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Question about the question

Stew Ashton, July 20, 2022 - 8:18 am UTC

The question reads "I have a refcursor for select query and return some rows . similarly i need to update the fetched rows as flagged."

What do you mean by "return some rows"? Once a REF CURSOR is opened, you either fetch the rows yourself or you return the cursor to the caller, who then fetches the rows. To "return rows" you use a table function.

If you really mean to return the cursor so that the caller can do the fetching, there is no way to intercept the rows to update them. You would have to fetch the rows yourself and update them, as Chis explained in his answer, and then return the rows you fetched using a pipelined table function. However, you are not allowed to change the data within such a function!

If I am reading your question correctly, any possible solution would be unusual and perhaps dangerous.

July 20, 2022 - 1:52 pm UTC

Good points

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Question about the question

More to Explore

SQL

REF_CURSOR select and then update flag for those records

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Question about the question

More to Explore

SQL