How to return row from function cursor and loop in oracle

Question

I want to run a dynamic query in oracle using a function/package that will return row/s which I can loop.

I have the function

create or replace FUNCTION FUNC_CHECK 
(
  I_SERVICE_ID IN NUMBER,
  I_count_id IN NUMBER,
  I_SRC_S1 IN NUMBER,
  I_SRC_E1 IN NUMBER,
  I_SRC_S2 IN NUMBER,
  I_SRC_E2 IN NUMBER,
  I_SRC_S3 IN NUMBER,
  I_SRC_E3 IN NUMBER,
  I_SRC_S4 IN NUMBER,
  I_SRC_E4 IN NUMBER,
  I_SRC_SEC IN VARCHAR2,
  I_SRC_NET IN VARCHAR2,
  I_SRC_LOC IN VARCHAR2,
  I_DST_S1 IN NUMBER,
  I_DST_E1 IN NUMBER,
  I_DST_S2 IN NUMBER,
  I_DST_E2 IN NUMBER,
  I_DST_S3 IN NUMBER,
  I_DST_E3 IN NUMBER,
  I_DST_S4 IN NUMBER,
  I_DST_E4 IN NUMBER,
  I_DST_SEC IN VARCHAR2,
  I_DST_NET IN VARCHAR2,
  I_DST_LOC IN VARCHAR2,
  I_PROTOCOL IN VARCHAR2,
  I_PORTS IN VARCHAR2,
  I_S_G IN NUMBER DEFAULT 0 
) RETURN SYS_REFCURSOR 
IS
    l_return   SYS_REFCURSOR;
BEGIN

  CASE I_S_G
        WHEN '0'
        THEN
           OPEN l_return FOR
                SELECT
                    t1.service_id
                    ,NVL(t1.SERVICE, 'All') SERVICE
                    ,t1.count_id
                    ,t2.table_id
                    ,t2.id AS POLICY_ID
                    ,t2.action
                    ,t1.summary table_name
                    ,t1.updated_by
                    ,t2.s1_1
                    ,t2.s1_2
                    ,t2.s2_1
                    ,t2.s2_2
                    ,t2.s3_1
                    ,t2.s3_2
                    ,t2.s4_1
                    ,t2.s4_2
                    ,t2.s_sec
                    ,t2.s_net
                    ,t2.s_loc
                    ,t2.d1_1
                    ,t2.d1_2
                    ,t2.d2_1
                    ,t2.d2_2
                    ,t2.d3_1
                    ,t2.d3_2
                    ,t2.d4_1
                    ,t2.d4_2
                    ,t2.d_sec
                    ,t2.d_net
                    ,t2.d_loc
                    ,t2.protocol
                    ,t2.ports
                    ,NVL(TO_CHAR(t1.expiry_date), 'Forever') EXPIRY_DATE
                    ,NVL(t2.note, 'Not specified') NOTE
                    ,t2.approved_by
                    ,NVL(
                        (
                            SELECT 'yes' 
                            FROM (
                                SELECT DISTINCT s1.protocol_id, s1.ports 
                                FROM sh.inner_s1 s1 
                                WHERE (s1.type = 'Service') 
                                START WITH s1.name = 'Blacklisted Ports' 
                                CONNECT BY PRIOR id = s1.parent_id
                            ) s1 
                            INNER JOIN sh.sys_list_protocols s2 ON (s1.protocol_id = s2.id) 
                            WHERE (s2.name = t4.name) 
                            AND (s1.ports = t2.ports) 
                            AND (ROWNUM <= 1)
                            )
                    , 'no') BLACKLIST,
                  t2.approved_date,
                  t2.seq,
                  CASE WHEN service_id = '0' and count_id = '0' THEN '2'
                     WHEN service_id != '0' and count_id = '0' THEN '1'
                     WHEN service_id != '0' and count_id != '0' THEN '0'
                  END table_authority
                  FROM sh.v_tables_group t1
                  INNER JOIN sh.v_table_policies t2 ON (t1.id = t2.table_id) 
                  LEFT JOIN sh.sys_list_protocols t4 ON (t2.protocol_id = t4.id)
                  WHERE ((t1.service_id = I_SERVICE_ID) OR (t1.service_id IS NULL) OR (t1.service_id = 0 ) )
                  AND ((t1.count_id = I_count_id) OR (t1.count_id IS NULL) OR t1.count_id = 0)
                  AND (t1.status = 'Processed')
                  AND (t2.rejected_date IS NULL)
                  AND ((t1.expiry_date IS NULL) OR (t1.expiry_date >= SYSTIMESTAMP))
                  AND (
                    (
                          ((I_SRC_S1 >= t2.s1_1) AND (I_SRC_E1 <= t2.s1_2))
                      AND ((I_SRC_S2 >= t2.s2_1) AND (I_SRC_E2 <= t2.s2_2))
                      AND ((I_SRC_S3 >= t2.s3_1) AND (I_SRC_E3 <= t2.s3_2))
                      AND ((I_SRC_S4 >= t2.s4_1) AND (I_SRC_E4 <= t2.s4_2))
                    ) 
                    OR (
                      ((I_SRC_SEC = t2.s_sec) OR ( (lower(t2.s_sec) = 'any' ) )) -- AND (t2.s_net IS NULL) AND (t2.s_loc IS NULL) ) )
                      AND ((I_SRC_NET = t2.s_net) OR (t2.s_net IS NULL))
                      AND ((I_SRC_LOC = t2.s_loc) AND (t2.s_net IS NOT NULL) OR (t2.s_loc IS NULL))
                      AND (t2.table_type_src = '')
                    )
                  )
                  AND (
                    (
                          ((I_DST_S1 >= t2.d1_1) AND (I_DST_E1 <= t2.d1_2))
                      AND ((I_DST_S2 >= t2.d2_1) AND (I_DST_E2 <= t2.d2_2))
                      AND ((I_DST_S3 >= t2.d3_1) AND (I_DST_E3 <= t2.d3_2))
                      AND ((I_DST_S4 >= t2.d4_1) AND (I_DST_E4 <= t2.d4_2))
                    ) 
                    OR (
                      ((I_DST_SEC = t2.d_sec) OR ( (lower(t2.d_sec) = 'any' ) )) -- AND (t2.d_net IS NULL) AND (t2.d_loc IS NULL) ) )
                      AND ((I_DST_NET = t2.d_net) OR (t2.d_net IS NULL))
                      AND ((I_DST_LOC = t2.d_loc) AND (t2.d_net IS NOT NULL) OR (t2.d_loc IS NULL))
                      AND (t2.table_type_dst = '')
                    )
                  )
                  AND (
                    ((t2.protocol = I_PROTOCOL) AND ( (t2.ports = I_PORTS) OR (t2.ports IS NULL) OR (lower(t2.ports) = 'any') ) )
                    OR
                    ((t2.protocol = 'ICMP') AND ( (t2.ports = I_PORTS) OR (t2.ports IS NULL) OR (t2.ports = 'All') ) )
                  )
                  ORDER BY CASE WHEN service_id = '0' and count_id = '0' THEN '2'
                     WHEN service_id != '0' and count_id = '0' THEN '1'
                     WHEN service_id != '0' and count_id != '0' THEN '0'
                  END asc, t2.approved_date desc, t2.seq
                  FETCH FIRST 1 ROWS ONLY;
        WHEN '1' THEN
        
           OPEN l_return FOR
            SELECT
                t1.service_id
                ,NVL(t1.SERVICE, 'All') SERVICE
                ,t1.count_id
                ,t2.table_id
                ,t2.id AS POLICY_ID
                ,t2.action
                ,t1.summary table_name
                ,t1.updated_by
                ,t2.s1_1
                ,t2.s1_2
                ,t2.s2_1
                ,t2.s2_2
                ,t2.s3_1
                ,t2.s3_2
                ,t2.s4_1
                ,t2.s4_2
                ,t2.s_sec
                ,t2.s_net
                ,t2.s_loc
                ,t2.d1_1
                ,t2.d1_2
                ,t2.d2_1
                ,t2.d2_2
                ,t2.d3_1
                ,t2.d3_2
                ,t2.d4_1
                ,t2.d4_2
                ,t2.d_sec
                ,t2.d_net
                ,t2.d_loc
                ,t2.protocol
                ,t2.ports
                ,NVL(TO_CHAR(t1.expiry_date), 'Forever') EXPIRY_DATE
                ,NVL(t2.note, 'Not specified') NOTE
                ,t2.approved_by
                ,NVL(
                    (
                        SELECT 'yes' 
                        FROM (
                            SELECT DISTINCT s1.protocol_id, s1.ports 
                            FROM sh.inner_s1 s1 
                            WHERE (s1.type = 'Service') 
                            START WITH s1.name = 'Blacklisted Ports' 
                            CONNECT BY PRIOR id = s1.parent_id
                        ) s1 
                        INNER JOIN sh.sys_list_protocols s2 ON (s1.protocol_id = s2.id) 
                        WHERE (s2.name = t4.name) 
                        AND (s1.ports = t2.ports) 
                        AND (ROWNUM <= 1)
                        )
                , 'no') BLACKLIST,
              t2.approved_date,
              t2.seq,
              CASE WHEN service_id = '0' THEN '2'
                 WHEN service_id != '0' THEN '1'
              END table_authority
              FROM sh.v_t1 t1
              INNER JOIN sh.v_table_policies t2 ON (t1.id = t2.table_id) 
              LEFT JOIN sh.sys_list_protocols t4 ON (t2.protocol_id = t4.id)
              WHERE ((t1.service_id = I_SERVICE_ID) OR (t1.service_id IS NULL) OR (t1.service_id = 0 ) )
              AND (t1.status = 'Processed')
              AND (t2.rejected_date IS NULL)
              AND ((t1.expiry_date IS NULL) OR (t1.expiry_date >= SYSTIMESTAMP))
              AND (
                (
                      ((I_SRC_S1 >= t2.s1_1) AND (I_SRC_E1 <= t2.s1_2))
                  AND ((I_SRC_S2 >= t2.s2_1) AND (I_SRC_E2 <= t2.s2_2))
                  AND ((I_SRC_S3 >= t2.s3_1) AND (I_SRC_E3 <= t2.s3_2))
                  AND ((I_SRC_S4 >= t2.s4_1) AND (I_SRC_E4 <= t2.s4_2))
                ) 
                OR (
                  ((I_SRC_SEC = t2.s_sec) OR ( (lower(t2.s_sec) = 'any' ) )) -- AND (t2.s_net IS NULL) AND (t2.s_loc IS NULL) ) )
                  AND ((I_SRC_NET = t2.s_net) OR (t2.s_net IS NULL))
                  AND ((I_SRC_LOC = t2.s_loc) AND (t2.s_net IS NOT NULL) OR (t2.s_loc IS NULL))
                  AND (t2.table_type_src = '')
                )
              )
              AND (
                (
                      ((I_DST_S1 >= t2.d1_1) AND (I_DST_E1 <= t2.d1_2))
                  AND ((I_DST_S2 >= t2.d2_1) AND (I_DST_E2 <= t2.d2_2))
                  AND ((I_DST_S3 >= t2.d3_1) AND (I_DST_E3 <= t2.d3_2))
                  AND ((I_DST_S4 >= t2.d4_1) AND (I_DST_E4 <= t2.d4_2))
                ) 
                OR (
                  ((I_DST_SEC = t2.d_sec) OR ( (lower(t2.d_sec) = 'any' ) )) -- AND (t2.d_net IS NULL) AND (t2.d_loc IS NULL) ) )
                  AND ((I_DST_NET = t2.d_net) OR (t2.d_net IS NULL))
                  AND ((I_DST_LOC = t2.d_loc) AND (t2.d_net IS NOT NULL) OR (t2.d_loc IS NULL))
                  AND (t2.table_type_dst = '')
                )
              )
              AND (
                ((t2.protocol = I_PROTOCOL) AND ( (t2.ports = I_PORTS) OR (t2.ports IS NULL) OR (lower(t2.ports) = 'any') ) )
                OR
                ((t2.protocol = 'ICMP') AND ( (t2.ports = I_PORTS) OR (t2.ports IS NULL) OR (t2.ports = 'All') ) )
              )
              ORDER BY CASE 
                 WHEN service_id = '0' THEN '2'
                 WHEN service_id != '0' THEN '1'
              END asc, t2.approved_date desc, t2.seq
              FETCH FIRST 1 ROWS ONLY;
     END CASE;

     RETURN l_return;
END FUNC_CHECK;

In my package I am doing

DECLARE 
    l_tables   SYS_REFCURSOR;
   l_table    VARCHAR2 (32767);
  v_emp_id  employee.emp_id%type;

  BEGIN
      DBMS_OUTPUT.put_line ('I_S_G: ' || I_S_G);
   l_tables := sh.FUNC_CHECK (I_SERVICE_ID,
  I_count_id,
  I_SRC_S1,
  I_SRC_E1,
  I_SRC_S2,
  I_SRC_E2,
  I_SRC_S3,
  I_SRC_E3,
  I_SRC_S4,
  I_SRC_E4,
  I_SRC_SEC,
  I_SRC_NET,
  I_SRC_LOC,
  I_DST_S1,
  I_DST_E1,
  I_DST_S2,
  I_DST_E2,
  I_DST_S3,
  I_DST_E3,
  I_DST_S4,
  I_DST_E4,
  I_DST_SEC,
  I_DST_NET,
  I_DST_LOC,
  I_PROTOCOL,
  I_PORTS,
  I_S_G);
      
   LOOP
      FETCH l_tables INTO l_table;

      DBMS_OUTPUT.put_line ('l_table: ' || l_table);
      EXIT WHEN l_tables%NOTFOUND;
   END LOOP;

   CLOSE l_tables;
   
    --FOR flow_row IN flow_cursor LOOP
      /*O_table_ID := tables.table_ID;
      O_POLICY_ID := tables.POLICY_ID;
      O_ACTION := tables.ACTION;
      O_SERVICE := tables.SERVICE;
      O_table_NAME := tables.table_NAME;
      O_EXPIRY_DATE := tables.EXPIRY_DATE;
      O_NOTE := tables.NOTE;
      O_APPROVED_BY := tables.APPROVED_BY;
      O_BLACKLIST := tables.BLACKLIST;
      IF O_ACTION = 'deny' THEN
          O_SPA := 'Prevention';
          O_SPN := 'Prevention #' || tables.table_ID || '.' || tables.POLICY_ID || ' - Prevented by ' || tables.APPROVED_BY || ' until ' || tables.EXPIRY_DATE;
          O_FPA := 'Prevention';
          O_FPN := 'Prevention #' || tables.table_ID || '.' || tables.POLICY_ID || ' - Prevented by ' || tables.APPROVED_BY || ' until ' || tables.EXPIRY_DATE;
      END IF;
      IF O_ACTION = 'permit' THEN
          O_SPA := 'table';
          O_SPN := 'table #' || tables.table_ID || '.' || tables.POLICY_ID || ' - Approved by ' || tables.APPROVED_BY || ' until ' || tables.EXPIRY_DATE;
          O_FPA := 'table';
          O_FPN := 'table #' || tables.table_ID || '.' || tables.POLICY_ID || ' - Approved by ' || tables.APPROVED_BY || ' until ' || tables.EXPIRY_DATE;
      END IF;*/
      
  END;

I understand I should not be using SYS_REFCURSOR but I am not sure how to implement it as a rowtype and loop through the results.

I was trying to use examples from https://blogs.oracle.com/oraclemagazine/working-with-cursors

The end result I need to store into the variables that are commented out to return back as output in the package ...O_table_ID .. O_...

UPDATE

This is what I have now but it is not working.

PROCEDURE ces (
  O_ERRORCODE OUT NUMBER,
  O_ERRORTEXT OUT VARCHAR2,
  O_EXEMPTION_ID OUT NUMBER,  
  O_POLICY_ID OUT NUMBER,
  O_ACTION OUT VARCHAR2,
  O_SERVICE OUT VARCHAR2,
  O_EXEMPTION_NAME OUT VARCHAR2,
  O_EXPIRY_DATE OUT VARCHAR2,
  O_NOTE OUT VARCHAR2,
  O_APPROVED_BY OUT VARCHAR2,
  O_BLACKLIST OUT VARCHAR2,
  O_SPA OUT VARCHAR2,
  O_SPN OUT VARCHAR2,
  O_FPA OUT VARCHAR2,
  O_FPN OUT VARCHAR2,
  I_SERVICE_ID IN NUMBER,
  I_count_id IN NUMBER,
  I_SRC_S1 IN NUMBER,
  I_SRC_E1 IN NUMBER,
  I_SRC_S2 IN NUMBER,
  I_SRC_E2 IN NUMBER,
  I_SRC_S3 IN NUMBER,
  I_SRC_E3 IN NUMBER,
  I_SRC_S4 IN NUMBER,
  I_SRC_E4 IN NUMBER,
  I_SRC_SEC IN VARCHAR2,
  I_SRC_NET IN VARCHAR2,
  I_SRC_LOC IN VARCHAR2,
  I_DST_S1 IN NUMBER,
  I_DST_E1 IN NUMBER,
  I_DST_S2 IN NUMBER,
  I_DST_E2 IN NUMBER,
  I_DST_S3 IN NUMBER,
  I_DST_E3 IN NUMBER,
  I_DST_S4 IN NUMBER,
  I_DST_E4 IN NUMBER,
  I_DST_SEC IN VARCHAR2,
  I_DST_NET IN VARCHAR2,
  I_DST_LOC IN VARCHAR2,
  I_PROTOCOL IN VARCHAR2,
  I_PORTS IN VARCHAR2,
  I_S_G IN NUMBER DEFAULT 0
)
AS
BEGIN
  O_EXEMPTION_ID := 0;
  O_POLICY_ID := 0;
  O_ACTION := '';
  O_SERVICE := '';
  O_EXEMPTION_NAME := '';
  O_EXPIRY_DATE := '';
  O_NOTE := '';  
  O_APPROVED_BY := '';
  O_BLACKLIST := '';
  O_SPA := '';
  O_SPN := '';
  O_FPA := '';
  O_FPN := '';

  DECLARE 
    l_e   SYS_REFCURSOR;

    EXEMPTION l_e%ROWTYPE;

  BEGIN
      DBMS_OUTPUT.put_line ('I_S_G: ' || I_S_G);
   l_e := sh.FUNC_c_e (I_SERVICE_ID,
  I_count_id,
  I_SRC_S1,
  I_SRC_E1,
  I_SRC_S2,
  I_SRC_E2,
  I_SRC_S3,
  I_SRC_E3,
  I_SRC_S4,
  I_SRC_E4,
  I_SRC_SEC,
  I_SRC_NET,
  I_SRC_LOC,
  I_DST_S1,
  I_DST_E1,
  I_DST_S2,
  I_DST_E2,
  I_DST_S3,
  I_DST_E3,
  I_DST_S4,
  I_DST_E4,
  I_DST_SEC,
  I_DST_NET,
  I_DST_LOC,
  I_PROTOCOL,
  I_PORTS,
  I_S_G);
      
      DBMS_OUTPUT.put_line (l_e);
  
   CLOSE l_e;
      
  END;

Answer 1

Here's an example which shows how to do it.

First, a function (yes, it DOES return sys_refcursor; why wouldn't it?):

SQL> CREATE OR REPLACE FUNCTION f_test (par_deptno IN dept.deptno%TYPE)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     rc  SYS_REFCURSOR;
  5  BEGIN
  6     OPEN rc FOR SELECT empno, ename
  7                   FROM emp
  8                  WHERE deptno = par_deptno;
  9
 10     RETURN rc;
 11  END;
 12  /

Function created.

Testing: note that you have to declare variables into which you'll fetch what refcursor returns. They, of course, match columns used in function's SELECT statement.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l_rc     SYS_REFCURSOR;
  3     l_empno  emp.empno%TYPE;
  4     l_ename  emp.ename%TYPE;
  5  BEGIN
  6     l_rc := f_test (10);
  7
  8     LOOP
  9        FETCH l_rc INTO l_empno, l_ename;
 10
 11        EXIT WHEN l_rc%NOTFOUND;
 12        DBMS_OUTPUT.put_line (l_empno || ': ' || l_ename);
 13     END LOOP;
 14  END;
 15  /
7782: CLARK
7839: KING
7934: MILLER

PL/SQL procedure successfully completed.

SQL>