X
  • ADF
    February 8, 2012

Passing comma separated string as bind variable for VO query's IN operator

Arunkumar Ramamoorthy
Consulting Technical Manager
Quite often, we want to pass a parameter to the bind variable in the
VO's query with a comma separated value, for the where clause with an IN
operator. However, normal SQL query that the VO contain interpret that
whole comma separated value as a single String and our usecase fail to
get fulfilled.

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

EmpViewDefault.jpg


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

EmpView.jpg


To validate our query, let us run the AM tester to check the result

AMTester.jpg


Validate the result by entering a comma separated ENAME list to the bind variable (SMITH,ALLEN,JONES).


AMTesterBindVar.jpg


Check out the query result

AMTesterQueryRes.jpg


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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.