Return updated rows from a stored function

Question

Im trying to select some rows from a Table in ORACLE and at the same time update the selected rows state. I found a way to do so with a stored function and Cursors but I cant manage to return the rows after using the cursor to update. This is my code:

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;

    CURSOR c_operations IS
        SELECT * FROM TABLE1
        WHERE STATUS != 'OK'
        FOR UPDATE OF TABLE1.STATUS;

BEGIN

    FOR r_operation IN c_operations
    LOOP

        UPDATE
            TABLE1
        SET
            TABLE1.STATUS = 'OK'
        WHERE
            TABLE1.ID_TABLE1 = r_operation.ID_TABLE1;

    END LOOP;

    COMMIT;      

    -- Missing conversion from cursor to sys_refcursor

    RETURN l_return;

END;

The update is working but Im still missing how to return the updated rows that are in the cursor (c_operations ).

Thank you.

Answer 1

Instead of a loop to update how about a bulk update collecting the updated ids. Then a table function from those returned ids.

create type t_table1_id is
   table of integer;

create or replace function set_table1_status_ok
   return sys_refcursor
is 
    l_results_cursor sys_refcursor;
    l_updated_ids    t_table1_id;
begin
    update table1
       set status  = 'Ok'
     where status != 'Ok'
    returning table1.id 
       bulk collect
       into l_updated_ids;

    open l_results_cursor for
         select * 
           from table1 
          where id in (select * from table(l_updated_ids));
    return l_results_cursor;
end set_table1_status_ok;

-- test 
declare 
   updated_ids sys_refcursor;
   l_this_rec  table1%rowtype; 
begin
    updated_ids := set_table1_status_ok();

    loop 
        fetch updated_ids into l_this_rec;
        exit when updated_ids%notfound;
        dbms_output.put_line ( l_this_rec.id || ' updated.');
    end loop;
    close updated_ids;
end ;

Answer 2

I'm going to make some assumptions:

1. id_table1 is the primary key of the table, so your RBAR (*) update affects only one row
2. id_table1 is numeric

If these assumptions are wrong you will need to tweak the following code.

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;

   l_id       table1.id_table1%type;
   l_upd_ids  sys.odcinumberlist := new sys.odcinumberlist();

   CURSOR c_operations IS
        SELECT * FROM TABLE1
        WHERE STATUS != 'OK'
        FOR UPDATE OF TABLE1.STATUS;

BEGIN

    FOR r_operation IN c_operations   LOOP

        UPDATE TABLE1
        SET    TABLE1.STATUS = 'OK'
        WHERE  TABLE1.ID_TABLE1 = r_operation.ID_TABLE1
        returning TABLE1.ID_TABLE1 into l_id;

        l_upd_ids.extend();
        l_upd_ids(l_upd_ids.count()) := l_id;

    END LOOP;

    COMMIT;      

    open l_return for
        select * from table(l_upd_ids);

    RETURN l_return;

END;

The key points of the solution.

• uses Oracle maintained collection (of number) sys.odcinumberlist to store the updated IDs;
• uses RETURNING clause to capture the id_table1 value for the updated row;
• stores the returned key in the collection;
• uses a table() function to casrt the collection into a table which can be queried in the ref cursor.

This last point is why I chose to use sys.odcinumberlist rather than defining a collection in the procedure. It's a SQL type, so we can use it in SELECT statements.

---

(*) Row-by-agonizing-row. Updating single records in a PL/SQL loop is the slowest way of executing bulk updates, and normally constitutes an anti-pattern. A straightforward set-based UPDATE should suffice. However, you know your own situation so I'm going to leave that as it is.

Answer 3

It looks to me like you don't need the initial cursor, since you're changing the STATUS of every row which is not 'OK' to 'OK', so you can do this is a simple UPDATE statement. Then use an OPEN...FOR statement to return a cursor of all rows where STATUS is not 'OK', which shouldn't return anything because you've already changed all the status values to 'OK'. I suggest that you rewrite your procedure as:

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
  l_return   SYS_REFCURSOR;
BEGIN
  UPDATE TABLE1
    SET STATUS = 'OK'
    WHERE STATUS != 'OK';

  COMMIT;      

  OPEN l_return FOR SELECT *
                      FROM TABLE1
                      WHERE STATUS != 'OK'
                      FOR UPDATE OF TABLE1.STATUS;

  RETURN l_return;
END;