By Arunkumar Ramamoorthy-Oracle on Feb 08, 2012
Ex. question in OTN thread : https://forums.oracle.com/forums/thread.jspa?messageID=10125366
To overcome this, Mohammad Jabr has written a blog entry with the help of Steve Muench's example #126. This has been achieved by using a TYPE and CASTing it to get the list.
In this article, we'll see another option to alter the query using regexp_substr, without having to use a TYPE and CAST.
Let us take an example of a VO created using EMP table with the following query.
SELECT Emp.EMPNO, Emp.ENAME, Emp.JOB, Emp.MGR, Emp.HIREDATE, Emp.SAL, Emp.COMM, Emp.DEPTNO FROM EMP Emp
We'll add a where clause to the VO's query with a bind variable that takes comma separated string as input. Here, we would be including regexp_substr function (Oracle DB >=10g), to split the comma separated string and return them as rows.
Now, our Where clause of the VO's query would be looking like
WHERE Emp.ENAME in (select regexp_substr(:Bind_Ename_Comma_Sep_List,'[^,]+', 1, level) from dual connect by regexp_substr(:Bind_Ename_Comma_Sep_List, '[^,]+', 1, level) is not null)Also, we'll add a Bind Variable Bind_Ename_Comma_Sep_List of String data type. Once after modifying the Where Cluase and after adding the bind variable, our VO would be looking like
To validate our query, let us run the AM tester to check the result
Validate the result by entering a comma separated ENAME list to the bind variable (SMITH,ALLEN,JONES).
Check out the query result
As it can be seen, we've passed 3 comma separated Enames to the bind variable, which in turn fetched only those records with the matching 3 Enames.