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.