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 following error

inconsistent datatypes: expected UDT got CHAR

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

>Solution :

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:

EXECUTE IMMEDIATE query_statement
BULK COLLECT INTO v_hrc_list
USING table_hrcs;

fiddle