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.


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.


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

Oracle SOA Suite Migration Services



  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:

    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…

  6. Online it solutions Is The Best SAP S4 Hana Simple Finance,Admin,Bw Hana Online Training Institute In Hyderabad by 10+yrs of real time industry experts call us@9701431363

  7. Thanks for sharing this information!
    I totally agree with you. Your information is very interesting and important. I really like this information.Our easy web plans company is famous in Advanced Oracle SOA Admin Online Training.
    If you want to see our training venue then click on links:
    Call Now: +91-9052483493
    Drop Mail: info@sacrostectservices.com

  8. Pratap says:

    While pooling from file to Db…if 3rd record failed so process will continue or it will rollback previous records as well..but don’t want to stop process if middle of records got failed..is it possible right.

Leave a Reply

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