How to pass parameters to IN clause in SQL query in DB adapter.

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

If You type and execute the below query at SQL prompt and it returns the data as 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/OSB 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 will not 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 in blue; 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.

Advertisements
This entry was posted in BPEL, Oracle Service Bus (OSB) and tagged , , , , , . Bookmark the permalink.

3 Responses to How to pass parameters to IN clause in SQL query in DB adapter.

  1. Sachin Thakkar says:

    I tried to follow your blog but i am getting problem in query generation.

    The parameters expected in place of ? and val are not getting replaced .

    Please give me a solution on this.

    Thanks and Regards,

    Sachin Thakkar

  2. Shital Zarkar says:

    Hi, Need to modify query to fetch details against list of employees at a time instead of single employee.My existing query has 3 where clauses, now i need to modify the query to have in clause. How to do that with dynamic list in IN clause?

    • Z Shital says:

      My existing query is :
      select a.last_name as LAST_NAME, a.first_name as FIRST_NAME from people a, assignments b where (a.employee_number = #EmpNum1 or a.npw_number = #EmpNum2) and trunc(sysdate) between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE and a.person_id = b.person_id and b.primary_flag = ‘Y’ and b.assignment_status_type_id 3 and b.effective_start_date = (select max(effective_start_date) from apps.per_all_assignments_f where person_id in (select distinct person_id from apps.per_all_people_f where (employee_number = #EmpNum3 or npw_number = #EmpNum4) and trunc(sysdate) between effective_start_date and effective_end_date) and primary_flag = ‘Y’ and assignment_status_type_id 3) and rownum < 2 How do i modify this to pass dynamic list to in clause?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s