Explicit cursor to SYS_REFCURSOR

Question

i my procedure i use 3 table as select for update. after getting select in cursor o update values in tables. and i have my values in cursor. my goal is to return values in cursor as SYS_REFCURSOR.

/* Formatted on 10/12/2022 12:13:44 (QP5 v5.388) */
CREATE OR REPLACE PACKAGE BODY sh1.test IS
    PROCEDURE sms2 (p_return_code OUT INTEGER, v_out OUT SYS_REFCURSOR) IS
        v_run_type         VARCHAR (20) := 'SMS';
        v_batch_size       INTEGER;
        v_threshold_size   INTEGER;
        v_retry_time       INTEGER;
        v_exception_list   VARCHAR (1);
        v_q_day            VARCHAR (10);
    BEGIN
       DECLARE
            CURSOR c_cur IS
                  SELECT p.outbound_event_id,
                         cl.seq_id,
                         -- select is long
                         
                         p.bill_account_code
                    FROM sh1.publish p, sh1.contact_list cl, sh1.actions_cfg ac
                   WHERE    
                         AND ROWNUM <= v_batch_size
                         AND ac.das_service = v_run_type
                         AND p.action_id = ac.action_id
                         AND p.retry_count <= ac.das_retry_count
                         AND p.outbound_event_id = cl.outbound_event_id
                         
                ORDER BY p.creation_date DESC
                FOR UPDATE OF
                    p.action_status,
                    cl.status,
                    p.mode_date,
                    cl.mode_date
                    SKIP LOCKED;

            c_cur_count   c_cur%ROWTYPE;
        BEGIN
            OPEN c_cur;

            FETCH c_cur INTO c_cur_count;

            IF c_cur%NOTFOUND THEN
                p_return_code := 2;
                RETURN;
            END IF;

            CLOSE c_cur;

            FOR cur_rec IN c_cur LOOP
                UPDATE sh1.publish p
                   SET p.action_status = '1', p.mode_date = SYSDATE
                 WHERE p.outbound_event_id = cur_rec.outbound_event_id;

                UPDATE sh1.contact_list cl
                   SET cl.status = 1, cl.mode_date = SYSDATE
                 WHERE cl.outbound_event_id = cur_rec.outbound_event_id;
            END LOOP;

            COMMIT;


            p_return_code := 0;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line ('Error code:' || SQLCODE);
                DBMS_OUTPUT.put_line ('Error message:' || SQLERRM);
                p_return_code := 5;
        END;
    END sms2;
END test;

i try OPEN v_output FOR SELECT *** option bu i can not use FOR UPDATE option.

Answer 1

The simpliest option to use procedure returning a SYS_REFCURSOR and processing the cursor afterwords would, basicaly, look like here:

--  
--  Procedure returning SYS_REFCURSOR
CREATE OR REPLACE PROCEDURE ref_cur_proc (
    p_param_1       IN VARCHAR2,
    p_param_refcur  IN OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_param_refcur FOR SELECT col1, col2, ...,  colx FROM tbl WHERE some_col = p_param_1;
END;

--  
--  Anonymous block using above SYS_REFCURSOR
DECLARE
    v_cursor_col1       tbl.col1%TYPE;
    v_cursor_col2       tbl.col2%TYPE;
    ...
    v_cursor_colx       tbl.colx%TYPE;
    --
    p_param_1           VARCHAR2(32) := 'Some Value';
    v_refcur            SYS_REFCURSOR;
BEGIN
    ref_cur_proc(p_param_1, v_refcur);  
    LOOP
        FETCH v_refcur INTO v_cursor_col1, v_cursor_col2, ... , v_cursor_colx;
        EXIT WHEN v_refcur%NOTFOUND;
        some_commands ...;
        ... ...
        ... ...
    END LOOP;
    CLOSE v_refcur;
END;