How to update ref cursor values in oracle?

Question

I want to fetch limited no. of rows using refcursor. then I need to update same set of records. is it possible?

create or replace PROCEDURE myproc (
        P_ROWCOUNT    IN    NUMBER,
        OUT_TXN_IDS   OUT   OF_CR_TYPE,
        P_CD_ERROR    OUT   NUMBER,
        P_DS_ERROR    OUT   VARCHAR2
    )
    AS
        V_TXNID NUMBER;

    BEGIN
        P_CD_ERROR := 0;
        P_DS_ERROR  := 'SUCCESS';

        OPEN OUT_TXN_IDS for
            SELECT id FROM table1 WHERE status='N' AND ROWNUM<=P_ROWCOUNT;
        
    EXCEPTION
        WHEN OTHERS THEN
            P_CD_ERROR :=  sqlcode;
            P_DS_ERROR := 'WF-ERROR - myproc - ' || substr(SQLERRM, 1, 200);
            RETURN;
END myproc;

I need to update same records to status Y after refcursor returns. can we do this. please suggest

Answer 1

I don't have your tables nor data so I simplified it a little bit, but - it should work nonetheless.

Initial statuses:

SQL> SELECT status, count(*) FROM table1 group by status;

S   COUNT(*)
- ----------
Y          7
N          7

Procedure: basically, you'd modify rows represented by ID returned by ref cursor.

SQL> DECLARE
  2     out_txn_ids  SYS_REFCURSOR;
  3     p_rowcount   NUMBER := 5;
  4     l_id         table1.id%TYPE;
  5  BEGIN
  6     OPEN out_txn_ids FOR SELECT id
  7                            FROM table1
  8                           WHERE     status = 'N'
  9                                 AND ROWNUM <= p_rowcount;
 10
 11     LOOP
 12        FETCH out_txn_ids INTO l_id;
 13
 14        EXIT WHEN out_txn_ids%NOTFOUND;
 15
 16        UPDATE table1
 17           SET status = 'Y'
 18         WHERE id = l_id;
 19     END LOOP;
 20
 21     CLOSE out_txn_ids;
 22  END;
 23  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT status, count(*) FROM table1 group by status;

S   COUNT(*)
- ----------
Y         12
N          2

SQL>