SQL & PL/SQL

How can I pass a list as bind variable in Oracle?

The following query work well in SQL Developer if I set ":prmRegionID=2".

SELECT COUNTRY_ID,
COUNTRY_NAME
FROM HR.COUNTRIES
WHERE REGION_ID IN (:prmRegionID);


The problem is that I can't find how to set ":prmRegionID=2,3".

I know that I can replace ":prmRegionID" by a substitution variable "&prmRegionID". The above query work well with"&prmRegionID=2" and with "&prmRegionID=2,3".

But with this solution, I lost all advantage of using binds variables (hard parse vs soft parse, SQL injection possibility, etc.).

Can some one tell me what is the approach suggest by Oracle on that subject? My developer have work a long time too find how but didn't found any answer yet.


Thank you in advance,


MB