CREATE OR REPLACE TYPE STRINGARRAY IS TABLE OF varchar2(255);

CREATE OR REPLACE PACKAGE client_utilities AS 
 
    TYPE flagged_varchar IS RECORD 
    ( 
        value   VARCHAR2(255), 
        flag    NUMBER(1) 
    ); 
     
    TYPE flagged_varchar_ref_cur IS REF CURSOR RETURN flagged_varchar; 
     
    PROCEDURE return_open_cursor 
    ( 
        p_flagged_varchar       OUT flagged_varchar_ref_cur, 
        p_unflagged_varchars    IN  STRINGARRAY 
    ); 
END client_utilities;

CREATE OR REPLACE PACKAGE BODY client_utilities AS 
 
    PROCEDURE return_open_cursor 
    ( 
        p_flagged_varchar       OUT flagged_varchar_ref_cur, 
        p_unflagged_varchars    IN  STRINGARRAY 
    ) 
    IS 
        l_varchar_table STRINGARRAY; 
        l_cursor  number; 
        l_return  number; 
        l_sql     varchar2(1000); 
    BEGIN 
        l_sql := 'SELECT /*+ CARDINALITY(unflagged_varchars, 100) */ COLUMN_VALUE, 1 
            FROM TABLE(CAST(:p_unflagged_varchars AS STRINGARRAY)) unflagged_varchars'; 
             
        l_cursor := dbms_sql.open_cursor; 
            
        dbms_sql.parse(l_cursor, l_sql, dbms_sql.native); 
        dbms_sql.bind_variable (l_cursor, 'p_unflagged_varchars', p_unflagged_varchars); 
        l_return := dbms_sql.execute(l_cursor); 
         
        p_flagged_varchar := dbms_sql.to_refcursor(l_cursor); 
 
    END return_open_cursor; 
 
END client_utilities;

declare 
  ret client_utilities.flagged_varchar_ref_cur; 
  l   STRINGARRAY := STRINGARRAY('1', '2', 'apple', 'banana'); 
  v   client_utilities.flagged_varchar; 
begin 
  client_utilities.return_open_cursor(ret, l); 
   
  loop 
    fetch ret into v; 
    exit when ret%notfound; 
    dbms_output.put_line(v.value || '##' || v.flag); 
  end loop; 
end;