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 FinishJoin 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



Service virtualization using OSB


service virtualization is one of the main feature of OSB. Service virtualization is the abstraction of a physical service through a proxy or intermediary service. In service virtualization, the service intermediary interacts with the service consumer and hides the physical location of the service provider. In other words, communication between the service provider and service consumer is done through a service intermediary, creating a gateway for application integration.






 In this Use case we will use one of the external web service which is available over web and will call that webservice from OSB. There are numerous webservices which are available in Online one  such kind webservice is Bank BLZ Service (http://www.thomas-bayer.com/axis2/services/BLZService?wsdl). By using this webservice we can fetch Bank Sort Codes (called Bank BLZ codes) for banks in Germany. In the request, you need to pass the BLZ code for any bank in Germany and it comes back with the bank details.


Create new project named BLZ service





Create folder structure like below


Copy the WSDL file from location http://www.thomas-bayer.com/axis2/services/BLZService?wsdl and place under WSDL folder

Creating Business Service

Drag and drop HTTP component from Technology palate to External Services lane.



Service Name: BS_BLZService
Location: browse through “BusinessService” folder we created in earlier step

Transport: http



Select “WSDL” from available options and browse to wsdl location

populates wsdl file with port after reading the WSDL file we have chosen in the previous step


populates the endpoint URI for the exposed web service to be invoked

Click Finish


Business Service is created on the External Services lane and this exists under “BusinessService” folder

Create Proxy Service


Drag and drop HTTP component from Technology palate to Proxy Services




 Fill up the mandatory details
Service Name: PS_BLZService
Location: browse through “ProxyService” folder we created in earlier step
Transport: http

Checking “Generate Pipeline” check box will create pipeline along with Proxy Service


We will use the same WSDL that we used to create business service



populates endpoint URI





Proxy Service is created on the Proxy Services lane and this resides under “ProxyService” folder





Connect Pipeline with business service


Double-click on the PS_BLZServicePipeline which opens up in new editor with .pipeline extension. Here Routing node inside route node is created automatically when we wire pipeline with business service


Testing the Service Bus project deploying into the IntegratedWeblogicServer





Request


Response