SOA - Multiple IN parameters in DB adapter

I came across a scenario, where I had to select data from a table using multiple parameters passed IN parameters.

When select operation is used in DB adapter , IN operator  is not available in the list of operators.That leaves me with the option of execute pure sqloperation.

Here schema gets generated on the basis of the query entered in SQL section where parameter EMPID appears to be a single element. This does not solve my purpose as I want to pass multiple employee Ids. Even when I define param element as maxOccurs=‘unbounded’, query only considers the first value. That means each #param becomes a bind variable in the query. You can't provide a list of values.

I found an approach to circumvent this by handling the multiple parameters at DB end.Following is the scenario

 1) Create a new SOA project with a synchronous BPEL

      2) Change the xsd of BPEL to conform to below structure. Here we aim to fetch employee details from the table on basis of one or more emp ids passed as the input to the composite.

      3) Create table as shown below

  

     4) Configure the DB adapter as below.

             Select Exceute pure sql operation

               In the next step enter the below query

SELECT EMPLOYEE_ID,EMPLOYEE_Name,EMPLOYEE_Dept from EMPLOYEE_Details WHERE  EMPLOYEE_ID IN (SELECT SUBSTR (DELIMITED_INPUT_STRING , DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) , INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL) - DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) )   FROM (SELECT #InputString || #Delimiter1 AS DELIMITED_INPUT_STRING ,  #Delimiter2 AS DELIMITER FROM DUAL)  CONNECT BY INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL)>0)

where

#InputString - Your Input String with delimiters (but no spaces) e.g. '11111,11112,11113’

#Delimiter1 - Assign the delimiter e.g. ','. This is to suffix above string with same delimiter.

#Delimiter2 - Assign the same delimiter e.g. ','

                  Click  on finish

5)  Once the DB adapter is configured our composite looks like this

                                      

6) In the BPEL,drop a invoke activity and create required input and output variables

                                           

      7)  Assign the parameters with delimiters (but no spaces) e.g. '11111,11112,11113’

I achieved the same by writing below XQuery which gave me the inputs in this pattern e.g. 11111 11112 11113 (separated by space)

declare variable $inputVariable.payload as element() (:: schema-element(get:process) ::) external;

declare variable $Inparam :="";

declare function local:funcTransformation_getempdetails($inputVariable.payload as element() (:: schema-element(get:process) ::)) as element() (:: schema-element(dbr:dbReferenceInput) ::) {

    <dbr:dbReferenceInput>

        <dbr:InputString>

         {

        for $EmpIds in $inputVariable.payload/get:EmployeeIDS/get:EmployeeID

        let  $Inparam :=fn:data($EmpIds)

        return $Inparam

       }

                </dbr:InputString>

        <dbr:Delimiter1>{fn:data(',')}</dbr:Delimiter1>

 <dbr:Delimiter2>{fn:data(',')}</dbr:Delimiter2>

            </dbr:dbReferenceInput>

};

Drop assign activity and use translate function to replace spaces in parameters to ,

 This gives me the desired string 11111,11112,11113

RESULT

Details corresponding to each emp id passed into the composite are fetched from DB

HAPPY LEARNING!!!!