oracle-databaseplsqloracle11gref-cursor

How to update ref cursor values in oracle?


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


Solution

  • 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>