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;
...||'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||'))'
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'
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;