How to Use SQL Query having IN Clause With DB Adapter

When you execute a syntactically correct SQL query having IN clause from SQL prompt, it works as expected.

Let’s assume you have a table employee with the structure given below and the table is populated with few records.

Name Null Type

------------------------------------------------------------------------------------------------

EMP_ID NOT NULL VARCHAR2(10)

EMP_DEPT VARCHAR2(30)

EMP_SALARY NUMBER(8,2)

EMP_DESIGNATION VARCHAR2(30)

EMP_FNAME VARCHAR2(30)

EMP_FNAME VARCHAR2(30)

EMP_ADDREDSS VARCHAR2(60)

You type and execute the query given below at SQL prompt and it returns the data expected.

SELECT EMP_ID, EMP_DEPT, EMP_SALARY, EMP_DESIGNATION, EMP_FNAME, EMP_FNAME
FROM EMPLOYEE
WHERE EMP_DEPT
IN ('SALES','FINANCE','TECHSERVICES','INFRACTURE')

If you use the above query with Oracle DB adapter in Oracle BPEL/ESB it works as expected.

Problem starts when you decide to pass the list of values for in query dynamically. You need to change the above query as the one given below:

SELECT EMP_ID, EMP_DEPT, EMP_SALARY, EMP_DESIGNATION, EMP_FNAME, EMP_FNAME
FROM EMPLOYEE WHERE EMP_DEPT
IN (?)

When you populate the input payload with value list for IN clause and execute the above query with DB adapter, DB adapter wont return anything. DB adapter wraps the parameter value by ' (apostrophe), so even if you pass a comma separated value list, the list would be treated as a single value by the DB adapter and the adapter returns nothing.

You need to write a SQL query like the one given below if you want to use IN clause in SQL query and pass the value list dynamically:

SELECT EMP_ID,EMP_DEPT, EMP_SALARY, EMP_DESIGNATION, EMP_FNAME, EMP_LNAME
FROM EMPLOYEE
WHERE EMP_DEPT IN (WITH VALUE_LIST AS
(SELECT ? val FROM dual)
SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1), (decode(instr(val, ':', 1, LEVEL) -1, -1, LENGTH(val), instr(val, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1) + 1) a
FROM VALUE_LIST CONNECT BY LEVEL <=
(SELECT(LENGTH(val) -LENGTH(REPLACE(val, ':', NULL)))
FROM VALUE_LIST) + 1)

See the portion of this query highlighted red; you don't need to change it. You need to customize the portion marked green as per your requirement.

While configuring DB adapter you need to the following steps:

• Select "Execute Custom SQL" as Operation Type and click on Next.
• Customize the above SQL and paste it in Custom SQL text box and click on Finish.
  

In this SQL, value list for IN clause should be delimited with a : symbol, If you want to use any other symbol as a delimiter you need to replace : with the symbol you want to use.

Before invoking the DB adapter, you need to create IN value list dynamically. Use XSL transformation to generate the dynamic value list and pass it to the DB adapter.

While generating the value list you don’t need to wrap character data with ' (apostrophe). To query all the employees from SALES, TECHSERVICES, INFRASTRUCTURE, FINANCE departments you need to create an IN clause value list as SALES:TECHSERVICES:INFRASTRUCTURE:FINANCE.

Limitations of DB adapter with this query:

• If you use SELECT * rather than giving the field list with SELECT, DB adapter configuration adapter won't be able to generate a correct XSD for DB adapter request.
• If you create a very big value list for IN query (greater than 4000 characters) than you need to break value list and invoke this query multiple times, otherwise you will get ORA-01704: string literal too long error.

Alternative Approach:

• You can write a PL/SQL stored procedure to implement the same functionality, only issue with PL/SQL is that you need to move the PL/SQL code whenever you move from one system to other i.e. development to system test to UAT to Production environments.