Inserting data from file into DB using Oracle SOA Database Adapter

In my previous blog I have explained about how to do configuration settings for Database adapter on Weblogic console and how to create a database adapter.

In this blog I am discussing about reading data from a xml file and inserting that data into database. So here I will be explaining

a. How to create a File adapter to pick a xml file from a particular location

b. How to create a Database adapter to insert data.

c. How to transform data from file to table.

I will start with creating a Application.

Open JDeveloper, Go to file select New, from that select Applications – from Items select SOA application and in next window give directory path where you want create this Application.

Click on next, in next window provide project name, click next.

Now from Composite template select “Empty composite”, click finish.

1. Now from Component palette drag File Adapter and place it left “Exposed service” side.

2. Click Next.

3. Give a proper name for File adapter. Ex: “ReadFileCustomerDetail”. Click Next

4. Click check box Define from operation and schema. Click Next.

5. Here I want read file data so I am selecting check box Read file. Click next.

6. In next step provide location from where file needs to be read.

There are two ways one is Physical and Logical path.

Physical path : If you select this option then whenever you want change your location you should come here and change it.

Logical path : If you select this option then you provide a logical name and every time whenever want to change file path then just change it in Configuration plan file. And also this value can also be changed at run time on Enterprise management console so need to change it through Jdeveloper.

Here I am selecting Physical path option and providing local file location.

In this window we also select other options like

a. Process file recursively.

b. Archive processed file : File will be archived and placed in provided location after successful read.

c. Delete files after successful retrieval : Once file is retrieved from location then that file will be deleted.

Click Next.

7. Here provide file format which you want read from provided location. Ex : *.xml, CustomerData*.xml.

We can provide file format which needs to be excluded.

Also if file contains multiple records and want to read single or multiple records at a time, that also can be selected here. I want to read one record at a time so I have provided one here.

Click Next.

8. In next step file age and file polling frequency should be provided. Click Next.

9. In next step format of file which needs to be read should be provided. Click on search. It will open other window.

10. In next step click red marked box.

11. Once click, it will open Import Schema File. Click search button and go to location where file format .xsd file besides. Import that file into project by following next steps.

12. Select CustomerDataRequests. Click OK.

13. Now in URL part CustomerDetail.xsd will appear. Click Next.

14. Click Finish.

After this a File adapter will be created in Exposed Services part.

15. Now I will create a BPEL process. While creating a BPEL process I am defining it later so I am selecting Define service later in Template and provide name for BPEL. Click OK.

16. Now drag a Database Adapter from Component palette into External Services part.

17. Give name to Service and Click Next.

18. In next step create a new Database Connection. Click on plus sign, it will open one more window. Here provide

a. Connection Name

b. Username

c. Pawword

d. Host Name

e. SID or Service name

f. Port number

Before clicking OK, click Test Connection and check connection was Success or Failure. If connection was Success then click OK.

19. Next step provide JNDI name which is created on Weblogic console. Click Next.

20. In next step will see multiple database operation.

Here I have selected insert only. Click next.

20. Here we should import a table in which we would like to insert Customer data. Click on Import Tables.

21. Select a Schema in which table is created and click on Query. Once we click on Query it will open list of tables under that schema.

22. Move that required table from left to right by clicking on arrow provided. Click OK.

23. Now will see selected table. Click Next.

24. In next window select a sequence which is created to generate Primary Key. Click on search, it will return list of sequences from that select required sequence. Click Next.

25. Click Finish. It will create a Database adapter.

26. Now we will have File adapter, BPEL Service and Database adapter.

Now connect File adapter, BPEL and database adapter.

After connecting all three we will see complete service like below.

27. Now double click on BPEL.

Drag a receive service from Web service palette and place it in empty BPEL. Receive service will receive file from File adapter.

28. After that double click receive activity and provide a valid name. Click check box Create instance. Click search button of partner link.

It will open other window, select partner link which is going to provide file to read. Here ReadFileCustomerDetail will provide file. Click OK.

29. To create a input variable which will give data from file, click plus to create a new variable. Give a proper name and Click OK.

30. Now drag Invoke service again from component palette.

Give a valid name, select a partner as Database invoke. Create input and output by clicking on plus. Click OK.

31. Drag a Transfom activity from Oracle Extensions palette into BPEL.

32. Click on Transform select input, output and give name for transformation. Click OK.

33. Now map incoming payload to input of DB adapter as per below screen.

34. Save all work done. We will see complete service like below screen.

35. Deployed service into local weblogic server.

36. Placed file into mentioned location with below data.

37. Data inserted into table successfully.

Oracle SOA Mediator

Mediator is one of the service component of the Oracle SOA Suite, there are four main functionality of Mediator

1. Routing : Mediator route incoming payload to the external service based on routing rule
2. Validate: Mediator performs XSD schema validation , it describes the structure of xml payload
3. Filter: Mediator defines a filter expression that specifies the service to be invoked if the payload satisfy filter condition
4. Transform: Mediator performs XSLT transformation , it transforms data suitable for the target                                                                                                                                                                                                                                                                    In this Demo we will be creating a composite application that routs the incoming payload to the BPEL process based on filter expression
   Step 1: Click File –> new –> project –> SOA Project                                                                                                                                                                                                                                                                                                                                                                     Click OK                                                                                                                                                                                                     Step 2: Enter the project name and click next                                                                                                                                                                                                                                         Click Finish                                                                                                                                                                                                     Step 3: Create a Schema for input and output                                                                                                                                                                                                                                                                                                                       Step 4: Expose a SOAP service, drag a Soap from component panel and drop it in exposed service                                                                                                                                                                                                                                                                             Name it as ProductDeliver, in WSDL URL click on Generate WSDL from schema as shown above                                                                                                                                                                                                                                                                                   In Interface Type choose Synchronous Interface  and click on + sign at input and name it as Request  , browse for input schema element
                                                                                                                                                                                                                                                                                                                         Choose the input element and click OKDo the same for output click on + sign at output and name it as Response, browse for output schema element and click ok                                                                                                                                                                                                                                                                                                                    Now u have exposed a SOAP Service                                                                                                                                            Step 5: Drag a mediator component from component panel and drop it in Components                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 and click OK                                                                                                                                                                                                                                                                                                                                                                                                           Now connect SOAP service with mediatorStep 6:Drag a BPEL process from component panel and drop it in components, name it as DLF and in Template choose Synchronous BPEL Process                                                          In input click browse and select input schema element, Click OK
                                                                                                  In output click browse and select output schema element, Click OK
                                                                                                    And uncheck Expose as a SOAP Service and click OKNow that u have create a BPEL Process (DLF), Follow same steps to create two more BPEL name it as Emirates and FedExNow connect this three BPEL to the mediator

Double click on mediator, u will find three section pointing to DLF, Emirates and FedEx BPEL

In DLF section click on filter to specifies routing rule

In routing rule we are going to compare payload element i.e. deliveryLocation  with India,if  true it will route to DLF BPEL

Click OK

Now assign payload to the input of DLF BPEL by click Assign value in mediator

After mapping payload with input of DLF BPEL

Click OK

Now that u have mapped input to DLF BPEL, u should get the out from DLF BPEL and mapped it to SOAP response

In Synchronous reply click on Assign value

Map the out of DLF BPEL to the SOAP response, Click OK

Now that u have done with DLF BPEL, do the same for Emirates and FedEx

BPEL and assign value for request and response

If deliveryLocation  is Dubai,it will route to Emirates BPEL else if deliveryLocation  is USA,it will route to FedEX BPEL

Step 6: Click on DLF BPEL , drag  assign from components panel and place it in BPEL

Double click on assign and concat product name with courier name as shown below

Do the same for Emirates and FedEx BPEL

Save all the changes and deploy the application

Step 7: Test the Application

1)

Request

Response

2)

Request

Response

3)

Request

Response

Oracle SOA Database Adapter

Database adapter –

This is one of the important part of Oracle SOA. Using database adapter we can do all Database operations like insert, update, delete, select, calling a stored procedure, polling records from DB and executing Pure SQL.

To call database adapter we should do some configuration on weblogic console like creating a datasource and creating a JNDI. Now we will see how to create a datasource and JNDI.

Creating Datasource :

http://localhost:7001/console login into local weblogic console using username and password.

1) After login to console go to Services and then click on Data Sources.

On this page provide a valid Datasource name. Ex : “TestDataSource”.

Give Jndi name. Ex : jdbc/db/nonxa/prod.

Database type : Can be given as per use. I am selecting here “Oracle”.

Click on Next.

2) On next page select Oracle driver version as per requirement. Here I am selecting default one and click on Next.

3) On next page there will not be anything to select or enter anything, just click on Next.

In the next page we should provide details like database name, host name, port, username and password.

After providing all correct details click on next.

4) In the next page we should check whether provided data in last page are valid are not, for we should click on Test configuration. If provided data are correct then we will see message as Connection test succeeded. Click on next.

If connection wasn’t successful then go back to previous page and make sure that valid data is entered.

5) Now select a target server, here I am selecting bam_server1. Click on finish.

Now we are done with creating datasource.

Creating JNDI :

1) To start creating with JNDI, click on Deployments, after that click on DbAdapter.

2) Now click on Configuration and then click on Outbound Connection Pools, after that click on New as shown picture below.

3) In next page just select a checkbox and click on next.

4) Give the proper name in place of JNDI name: eis/db/nonxa/qa and click on finish.

5) Now we should provide Datasource name to created JNDI. Click on javax.resource.cci.ConnectionFactory.

6) Click on created JNDI name : eis/db/nonxa/qa.

7) In the next page we should change two properties

1. a) xADataSourceName : As created datasource.
2. b) sequencePreallocationSize Note : By default this value will be 50. If we keep this default value as it is then whenever we create a sequence for a table then every time that sequence value will be incremented by 50.

Ex : If initial value of sequence is 1000 then next value will 1050, 1100, 1150 and so on.

Note : Simply if we provide these two values and click on save will not work. First provide sequencePreallocationSize as 1 or as per requirement and click enter. Then next provide xADataSourceName value and again click on enter. And then click on save. If we do in this way then only changes will effective.

Database Adapter :

Integration of Database Adapter

Drag a Database Adapter from component palette and drop it in exposed services.

Step 1.

Click Next

Step 2.

Enter Service name and click next.

Step 3.

Here click on plus icon, it will open new window to create new connection.

Step 4.

Enter the required detail as provided in above image like

1. Database username, password.
2. Hostname
3. Port number
4. SID or Service name as per requirement

Before clicking OK click on Test Connection and check whether given information’s are correct. If connection is Success! then click on OK.

Step 5.

In this step we should select which operation we want to do on Database.

1. Call a stored procedure or function – If we select this then we call a selected stored procedure.
2. Perform as operation on a Table – In this operation we can read, write, update, delete records from a table.
3. Poll for New or Changed Records in a table – Polling records from table. Polling can be done in asynchronous/synchronous way, for that we should select one check box.
4. Execute pure SQL – This property will allow us to write a query as per our requirement.

Note : If we select Select check box under Perform an operation on a table then we can’t select records as per our requirement because this will allow us select records as per Primary key. So if we require to select records as per for Ex : first name or last name then better use Execute Pure SQL, here we can write query as per our “where” condition.

Here I am using Insert only operation because I am going to insert data into table. Click Next

Step 6.

Here select a schema from which a particular table should be selected. Click on Query which will schema and it will list of tables under it. Now select a particular table which are listed after querying and click on arrow.

Step 7.

After clicking on arrow table will be moved from left hand side to right. Click OK.

Step 8.

After click OK will see screen like above, click Next.

Step 9.

I don’t have any relationships to be created with other table so its showing like “No Reachable Relationships”. If there two tables selected and there is relationship required then click Create… and create required relationship. Click Next >.

Step 10.

Here it will show column which are present under selected table. Here we select and deselect required and not required fields and Click Next >.

Step 11.

In above screen we select Sequence which is will used to generate Next Primary Key for every record which is going to be inserted. Click on Search, it will give list of Sequences from that select required Sequence. Click Next >.

Step 12.

Click Finish. It will create a Database Adapter in BPEL.