How to pass in a comma separated list of values as a Parameter input in Oracle

I have a simple select query that I would like to pass in a Parameter in the WHERE clause which contains more than one value, (comma separated list) and the query shows results for each comma separated value ? I need to know what the syntax / method for this is in Oracle SQL.

So in the Query below my Parameter is (:ENTITY_CODE) and I want this parameter to pass in a comma separated list of values

SELECT
C.BUSINESS_UNIT__C AS Region
,E.ENTITY_NAME AS EntityName
,C.COMPANY_NAME__C AS CompanyName
,C.COMPANY_NUMBER__C AS CompanyNumber
,C.ACE_OWNER_NUMBER__C AS OwnerNumbe
FROM
SFB_CASE C 
JOIN COMPANY_DIM CO ON C.COMPANY_NUMBER__C = CO.COMPANY_NUMBER
JOIN GBL_ENTITY_DIM E ON CO.GBL_ENTITY_KEY = E.GBL_ENTITY_KEY
WHERE
E.ENTITY_CODE = (:ENTITY_CODE)'

Answer

You can use hierarchy query as follows:

WHERE
E.ENTITY_CODE IN (SELECT REGEXP_SUBSTR(:ENTITY_CODE,'[^,]+',1,LEVEL) FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(:ENTITY_CODE,',') + 1))

Advertisement

Answer