passing string

[luciffer]

ok so far i am using the USING clause for dynamic bind variable query.

sql_stmt := 'SELECT id FROM caw_communities_x WHERE id IN (:1)';
EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO CommunityList USING communities;

where communities is the parameter passed to the function as a comma delimited list. But this query doesn't work this one will however work


sql_stm := 'SELECT id FROM caw_communities_x WHERE id IN (' || communities || ')';
EXECUTE IMMEDIATE sql_stm BULK COLLECT INTO CommunityList;


but i'd like to do it with the dynamic bind, is it possible?

[jmodic]

No, using bind variable as the parameter for the IN operator is not possible like this. But you can revrite the query like this to use the comma delimited list as bind variable (assuming column ID is of NUMBER type):

sql_stmt :=
'SELECT id FROM caw_communities_x
WHERE INSTR(:1, '',''||TO_CHAR(id)||'','') > 0';

EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO CommunityList
USING ','||communities||',';

The execution plan for this query might of course be totaly different (it will perform full table scan), but if the number of records in your table is relatively small and/or the number of elements in your in-list parameter is relatively large the performance might be acceptable.