Home » Oracle SOA » Inserting data from file into DB using Oracle SOA Database Adapter

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.

Oracle SOA 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.

DBInsert

2. Click Next.

DBInsert1

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

DBInsert2

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

DBInsert3

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

DBInsert4

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.

DBInsert5

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.

DBInsert6

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

DBInsert7

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

DBInsert8

10. In next step click red marked box.

DBInsert9

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.

DBInsert10

DBInsert11

12. Select CustomerDataRequests. Click OK.

DBInsert12

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

DBInsert13

14. Click Finish.

DBInsert14

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.

DBInsert15

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

DBInsert16

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.

DBInsert18

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

DBInsert19

20. In next step will see multiple database operation.

Here I have selected insert only. Click next.

DBInsert20

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

DBInsert21

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.

DBInsert22

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

DBInsert23

23. Now will see selected table. Click Next.

DBInsert24

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.

DBInsert25

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.

DBInsert26

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

DBInsert27

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.

DBInsert28

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

DBInsert29

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

DBInsert30

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.

DBInsert31

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.

DBInsert33

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

DBInsert34

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

DBInsert35

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

DBInsert36

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

DBInsert37

35. Deployed service into local weblogic server.

36. Placed file into mentioned location with below data.

DBInsert38

37. Data inserted into table successfully.

DBInsert39

To know more about Oracle SOA suite migration services, please visit

Oracle SOA Suite Migration Services

 

Comments

  1. fr says:

    Can you show one simple example where multiple rows in a .txt file is read by a file adapter and inserted into table using Db adapter…

  2. Really This blog is awesome! I was searching over search engines and found your blog site. Well I like your high-quality blog site design plus your posting abilities. Keep doing it.

  3. Amar Latthe says:

    Thanks for comment and appreciation.

  4. Vidyut Verma says:

    Very detailed writeup….. really appreciate the effort. I’m gonna try this.

    Can u point to .xsd file also which can be used.

  5. Aravind says:

    Hi,
    Can you please explain a test case how to apply data filter in this program, my requirement is i need to copy data from one file with some filter condition and paste into another file….
    Thanks in advance…

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*