PL/SQL open cursor for query string with varchar2 array

I want to use a array in open c1 for sql-statement (in plsql)

array is table of varchar2;.

array is filled by another function:

OPEN v_c1 FOR query_statement;
            LOOP
                v_hrc_list.extend; 
                FETCH v_c1 into v_hrc_list(v_hrc_list.count);
                EXIT WHEN (v_c1%NOTFOUND);
            END LOOP;
        CLOSE v_c1;

I tried:

query_statement = `...||'WHERE MEMBER OF (select * from table (:table_hrcs))'`

OPEN v_c1 FOR query_statement USING table_hrcs; 

and I got the following error:

> Inconsistent datatypes: expected UDT got CHAR

I also tried

...||'WHERE MEMBER OF (select * from table ('||table_hrcs||'))'

You're mixing up two ways of referring to members of a collection.

With the collection type you appear to have you can either do:

' ... WHERE your_column IN (select * from table (:table_hrcs))'

or more simply:

' ... WHERE dummy MEMBER OF :table_hrcs'

fiddle

You may see a performance difference between the two with large amounts of data.

Also notice that the result array ends up with an empty last element.

Your example doesn't need to use a cursor and for-loop, you can bulk-collect directly into the target collection (which then doesn't need to be initialised, and doesn't have the empty element):

EXECUTE IMMEDIATE query_statement
BULK COLLECT INTO v_hrc_list
USING table_hrcs;

fiddle