DB Adapter Select Query Dynamic IN Parameters

                

I came across a scenario where we need to pass IN parameters dynamically and number of parameters are not fixed. If we design query like below it will accept only one argument

Select * from CUSTOMER where CITY IN(#IN_City)

As we don't know the number of arguments at design time, one option we  tried is inject multiple arguments as comma separated string ( Pune,Banglore,Hyderabad). But DB adapter tries to read the argument as a bind parameter and not SQL string. Then we changed query as below 

Select * from CUSTOMER where CITY IN (

select regexp_substr(#IN_City,'[^,]+', 1, level) from dual

    connect by regexp_substr(#IN_City, '[^,]+', 1, level) is not null

           );

Now we can pass the same comma separated string as argument at both the places. 

Select * from CUSTOMER where CITY IN (select regexp_substr('Pune,Banglore,Hyderabad','[^,]+', 1, level) from dual
    connect by regexp_substr('Pune,Banglore,Hyderabad', '[^,]+', 1, level) is not null
);

To design argument as comma separated string we will use “oraext:create-delimited-string” function in BPEL. Design Synchronous BPEL which takes array of arguments and use DB adapter pure SQL operation to execute the query. I am using SOA 12.2.1.3 version and Oracle DB 11g.

Create a SOA Project 

Create Synchronous BPEL process

Change the input xsd to take multiple arguments so added maxOccurs="unbounded" for input element

Right click on the External References -> Insert Adapters -> Database

Select Execute Pure SQL as Operation Type

Enter the query in the SQL box, it will automatically generate a XSD based on the query.

We can see the input and output elements. If we observe for input elements type is empty 

 Provide the type as xs:String

Keep default settings as it is and click Finish. Join BPEL and DB Adapter. In BPEL use invoke activity and create DB input and output variables.

Drag and drop transform activity between receive activity and invoke activity

In transformation take Source as input variable and Target as DB adapter input variable.

In transformation pass same expression for both arguments

Now deploy the code and test. While testing give array value as 2 so input will take two values

If you observe the flow trace we can see DB input as comma separated string