Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE TYPE r_col_val IS OBJECT (l_col_val VARCHAR2(250 byte));
CREATE OR REPLACE TYPE t_col_val IS TABLE OF r_col_val;
-- made up; not shown in question
CREATE OR REPLACE TYPE r_col_head IS OBJECT (l_col_head VARCHAR2(250 byte));
-- made up; not shown in question
CREATE OR REPLACE TYPE t_col_head IS TABLE OF r_col_head;
-- made up; not shown in question
CREATE OR REPLACE FUNCTION 
GET_REF_CUR
    (
    p_stmt IN VARCHAR2
    )
RETURN sys_refcursor
IS
    l_rc sys_refcursor;
BEGIN
    open l_rc for p_stmt;
    return l_rc;
END;
/
CREATE OR REPLACE FUNCTION 
GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

    l_val t_col_val := t_col_val();
    l_col t_col_head := t_col_head();

    l_cursor        SYS_REFCURSOR := p_cursor;
    l_cursor_id     INTEGER;
    l_dummy         INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB2;
    l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        l_col(l_col.count) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

    /* COLUMN VALUES */
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;
    
        FOR i IN 1 .. l_col_cnt
        LOOP
    
            l_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
            --DBMS_OUTPUT.PUT_LINE(l_dum_val);
            l_val(l_val.count) := r_col_val(l_dum_val);
    
        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    RETURN l_val;

END;
/
DECLARE

l_sql_stmt VARCHAR(10000) :=
q'!
    SELECT
        SYS_CONTEXT('USERENV','OS_USER') AS OS_USER ,
        SYS_CONTEXT('USERENV','SESSION_USER') AS SESSION_USER,
        SYS_CONTEXT('USERENV','ISDBA') AS ISDBA,
        SYS_CONTEXT('USERENV','SID') AS SID,
        SYS_CONTEXT('USERENV','CURRENT_SQL') AS CURRENT_SQL,
        SYS_CONTEXT('USERENV','DB_NAME') AS DB_NAME,
        SYS_CONTEXT('USERENV','HOST') AS HOST,
        SYS_CONTEXT('USERENV','IP_ADDRESS') AS IP_ADDRESS,
        SYS_CONTEXT('USERENV','SERVICE_NAME') AS SERVICE_NAME
    FROM
        DUAL
!';

l_cursor        SYS_REFCURSOR;
l_cursor_id     INTEGER;
l_dummy         VARCHAR2(50);

TYPE t_cur_head IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_head t_cur_head;

TYPE t_cur_val IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_val t_cur_val;    

BEGIN

l_cursor := GET_REF_CUR(l_sql_stmt);

IF l_cursor%ISOPEN
THEN

    /* Header fetch works fine */
    /*
    SELECT * 
    BULK COLLECT INTO l_cur_head 
    FROM TABLE (GET_REF_HEAD(l_cursor));

    FOR i IN 1 .. l_cur_head.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_head(i));
    END LOOP;
    */

    /* Values fetch now works */

    SELECT * 
    BULK COLLECT INTO l_cur_val 
    FROM TABLE (GET_REF_VAL(l_cursor));

    FOR i IN 1 .. l_cur_val.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_val(i));
    END LOOP;

END IF;

END;
/
1 rows affected

dbms_output:
apache
FIDDLE_OCRBHLXZOLQWECZYHRPI
FALSE
35

XEPDB1
fiddle-oracle18xe.localdomain
192.168.43.23
xepdb1