Mediator Vs Oracle Service Bus (OSB) in Oracle SOA Suite

In this post i am going to clear the basic difference between Mediator and Oracle Service bus. There is always some confusion between these two for new comers. Both of these components can be used for mediation purpose.

 Both mediator and Oracle Service bus can perform Interface and data mapping, filtering and routing etc but the scope of both these components are different. For Intra-composite mediation

i.e connection of components within a give composite we use Mediator Component while for Inter-composite mediation we use Oracle service bus i.e to connect different composites. While doing this OSB can also perform other basic tasks like , Protocol translation service pooling etc.
 To check out the usage of Mediator you can check this post
Mediator in Oracle SOA Suite

Read More

IO Error: Connection reset by peer: socket write error, Connection Closed SQL Developer

From couple of days i was facing IO Error: Connection reset by peer: socket write error, Connection Closed  in my SQL Developer. I connected with Remote server DB. After wasting much of time i got the solution, Now i am sharing it, if it could save time of who is facing this issue. I hope it would resolve the issue. 

The problem was due to Proxy, I was using Proxy for internet access but i did not used this proxy in SQL Developer. You should give proxy and must restart  your SQL Developer if you are using Proxy. 

To set Proxy go to Tools->Preferences->Web Browser and Proxy and set your Proxy there. In host name give your Proxy, in Port give port might be 8080 and in exception give any remote IP on which you facing this issue, Now restart your SQL Developer and try again, Problem will be no more.
Are you still getting problem? then try this solution too. 
SQLDeveloper is not connecting to remote database

Read More

Calling BPEL Process using Database Adapter, Automatically Invoking BPEL Process

In this post i will show how we can call BPEL Process using DB Adapter. This post is a second part of my Previous post http://technasir.blogspot.com/2013/10/polling-in-oracle-soa-database-adapter.html . I will continue from previous post. I suppose you have created DB Adapter as i did in my previous post. Your requirement could be different, but scenario could match.

1) After creating DB Adapter your composite will look like this which also have BPEL Process that you want to invoke using this DB Adapter

2) Connect this DB to Your BPEL as shown below

3) Save your composite and open your .bpel name file. Delete any old Partner link, keep your newly created Adapter as shown in below snap

4) Now connect your DB Adapter that will invoke BPEL Process to receiveInput activity and a popup will appear, create a variable and click apply and then ok

5) Your BPEL file will look like below

6) Now go back to your Composite.xml file and delete old service it will be no wired now

7) Now save your Project and Build it. You might get an error like what i got. do not worry just double click on the error and select variable that is causing this error just delete those lines as shown below

8) Now rebuild and deploy it. Should be no error this time

Now every time when any changes would happen in your polling table, your DB Adapter will invoke this BPEL Process.

Read More

Polling in Oracle SOA Database Adapter

A very useful feature of Oracle data base adapter is polling. It tell us about any changes in particular table on which we want to poll. Using this feature we can do lot of things according to our logic and our requirement. I will split this post in two part

1) How to Configure DB Adapter for Polling
2) How to Invoke BPEL Process using DB Adapter, or Automatically

Steps to configure polling in SOA DB Adapter

1) Drag and Drop a Database Adapter in Exposed services, left side, on Service name window give Service Name

2) Create a Connection to your DB and give JNDI name that you have set in your Weblogic

3) In Select Operation Type window select "Poll for new or changed records in a table"

4) In Next Window Import tables and select table on which you want polling

5) In next Window check Primary key for detail see below snap

6) Click next and you will reach on attribute filtering window. check if you want any other attribute. i only have one. Read detail for more info

 7) Click next and choose what you want after reading, i am writing on file, that are present on my server

8) Click next you have to give location of your file

9) On next screen set your polling frequency and i set 10

10) Click next, next and finish it on Finish window your Adapter now configured

In next tutorial i will show how you could invoked BPEL Process using this DB adapter
Here is the link of next Part 
http://technasir.blogspot.com/2013/10/calling-bpel-process-using-database.html 

Read More

Create Sequence in SQL Oracle, Create Trigger on Insert and Update in Oracle

Sequence in Oracle SQL. Following is the basic code to create a sequence in Oracle SQL

CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;

Trigger on insert and update on any table 

Following code can be used to create a trigger on any table, It will invoke and do what you want when any value will get inserted and update in table

create or replace trigger trigger_name before insert OR update on table
for each row begin

write you logic here what should happen when trigger gets invoke.
end;

Read More

Mater Detail Relationship In DB Adapter BPEL. 1 to M Relationship

In this post i will show how to create a Parent child relationship in Database Adapter. Create a new SOA Application in your JDeveloper with Empty Composite. Now follow these steps to configure a DB Adapter by using Parent child relationship

1) Drag and Drop a DB Adapter in External References and give its name.

2) Click next and create a DB Connection and set your JNDI name according to your Weblogic server configured JNDI name for this DB

3) Click next. Operation Types screen will appear now i want to insert data, however your purpose can be different, just choose according to your requirement

4) On next screen click on Import table and choose your master and detail table from pop up and then click ok

5) In next two steps or depend on number of tables you selected, Choose primary keys for your tables, just primary keys not the primary foreign keys.

6) for second table, i only have two table

7) Now click next, Relationship window will open, click on create to create a relationship a popup will appear and select things according to your choices. Below is my case

8) Click next, Here you can see relationship of tables

9) Click next and choose columns which you want to select.

 10) Click next and choose defaults on next screens and click finish at the END that it.

Read More

Oracle SOA Suite 11g Installation, Maintenance, Purging, Performance tuning, troubleshooting and Administration

I found very useful guide from Oracle which covers Oracle Fusion Middleware, Specially About Oracle SOA Server. It covers everything that we should know about Oracle SOA suite. Following are some main points of this document. 

1) Installation of Oracle SOA Suite and Schema's 
2) Upgrade of Oracle SOA Suite  
3) Administration of Oracle SOA Suite
4) Performance tuning of Oracle SOA Suite
5) Purging techniques in Oracle SOA, Fusion Middleware, Step by Step Purging guide
6) Troubleshooting of Oracle SOA Infrastructure 

Document name is 
Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide
Doc ID is : 1384379.1

You can find it on https://support.oracle.com. 

Read More

Oracle Database 12C and some of its features. Database 12c and Cloud Computing

Oracle Database 12c released with new and interesting features. Oracle is saying that it is a multitenant database for the cloud. Oracle continuously improving and enhancing its products and technologies to fulfill the customer requirements. Oracle provide a new architecture for its database for cloud called Oracle multitenant.
    According to  Oracle President Mark Hurd
                     “Just as virtualization lets you turn a physical
                      server into a collection of virtual machines,
                      Oracle Multitenant lets you turn a physical
                      Oracle database into a collection of pluggable
                     databases, each of which looks and feels like
                     a physical Oracle database to the applications
                    with which it is associated,” .
 Application can run in a secure isolated environment. Oracle added more than 500 feature to transform how organizations store, manage and use information's. following are some prominent features

1) Automatic data optimization
2) Defence in-depth security
3) High availability for database cloud
4) Aministrative effeciencies for DBAs
5) Robust analysis of Big data

Read More

Integration of Oracle EBS with Siebel CRM, Mediator in Oracle BPEL, Integration of Two DBs

In this post i will show how to use Mediator in BPEL Process and Transformation of attributes. This post is part of my previous posts series. You should also go through previous posts to fully understand the scenario. Here is the link of previous post
                             Inserting data using DB Adapter 
lets start on Mediator and Transformation in BPEL Process.

1) This is the composite so far, you will be here if you have followed my previous posts

2) Drag and Drop a Mediator component in Components section of your composite. A pop up will appear, perform the following changes

3) For Input click on the Search icon, front of Input field, In this section we will choose XSD Out schema of our first DB Adapter that is pulling data from Apps

4) Click OK your composite will look like following snap

5) Wired Mediator with your BPEL Process like below

6) Now Wired your Mediator with two DB Adapters that are inserting data into your DB like below

7) Double Click on Your Mediator component a .mplan file will open

8) Click on Filter Icon as show below. Then insert your OutParameter in your Expression as show below

9) Click OK and then click on Transform Icon as shown below and then create a new mapper file as shown below

10) Click OK you BPEL will look like below in Snap

11) Now Map your header table fields with your insert table fields. You can also assign hard code values by using different function as shown right.
For example my first attribute in a sequence just search sequence from right and drop in Mid section double click it and give the values like below. In data source field you have to give your data base connection string

 12) Drag and Drop for-each loop and wire it as shown below, we can have multiple records in our data set so it is required

13) Save all changes, go back to your BPEL process which will look like this. If your Mediator is displaying on Left side no worry, just right click on it and from Display, click move to opposite swim line as shown below

14) Drag and drop another Invoke service and drop it below AssignOutput, A pop up will appear, create an input parameter like below

15) Now double click on your AssignOutput activity to open mapping, because you have to assign Output parameter to input of newly created Invoke activity, like below

16) Your composite is ready to deploy. Just deploy it on server run it, it will fetch data from apps and will insert it into Siebel DB or your another DB, It will also show output on your EM console like below

 So in this series of Posts i have covered

1) Calling a Stored Procedure using DB Adapter which have out parameter.
2) Stored Procedure with Master and Detail Data
3) Invoking and Fetching Data using BPEL process and DB Adapter
4) Fetching data from One DB and Inserting it into another DB using DB Adapter
5) Integration of EBS with Siebel CRM
6) Using Mediator in BPEL Process
7) Transformation in BPEL Process

Read More

Inserting data using DB Adapter in BPEL SOA Suite

In this Post i am going to show how to insert data using DB Adapter in SOA Suite. This is a integration of Oracle Apps with Siebel CRM. First i will show you how to configure DB Adapter to insert data in it. I have two tables master and detail. I will show you only master table insertion wizard, however this can be done in combine as well. You can also create relationship between table in DB Adapter but i am not going into much detail. Please see following posts for basic development and pulling data from Apps DB. I have done this already so you should complete this. Now i will insert that apps data to Siebel CRM table.
1) Stored Procedure calling using DB Adapter
2) Invocation of Stored procedure using BPEL Process
 Lets start configuring DB Adapter for master table insertion data from Apps to Siebel DB

1) Drag and Drop a Database Adapter in you composite. Give it a name click next and create DB connection of DB where you want to insert this Data, i am inserting this data into Siebel DB. Make sure you should create a Data source in your Weblogic and you should give your JNDI name according to that otherwise it will not work.

2) Click next and select insert only as shown below

3) Click next and choose your schema and select table in which you want to insert data and click ok

4) Click next, I am not making any relationship, so click next and on Attribute filtering screen choose attribute, where you will insert data.

 5) Click next and click on deselect all as shown below to avoid any extra attribute if you do not want to include

6) Click next and then again next without any changes then finish . Your DB Adapter to insert data into Other DB is ready.

In next tutorial i will show how to use mediator to transform and insert data into DB using Mediator component.
Next Tutorial http://technasir.blogspot.com/2013/08/integration-of-oracle-ebs-with-siebel.html

Read More

Store Procedure invocation using BPEL Process in SOA Suite

In this post i will show you how to call and get Data using DB Adapter that we have developed previous post. You can check it out here Store Procedure with Out Parameter. In this Post we will develop a BPEL Process to Invoke this stored Procedure.

1) Drag and Drop a BPEL Process in component section. Following snaps shows how to do it. In input parameter select input parameter from XSD that is generated by your DB Adapter.

 2) For Output select output from xsd as shown below

3) After this click OK. Wire BPEL Process with DB Adapter. Your JDeveloper should look like this

4) Double click on EBSPricelistBPEL to open .bpel file. here you will apply little logic

5) Now drag and drop Invoke service from your right between receiveinput and replyoutput. Wire this with DB Adapter, A pop up will appear. give it a appropriate name and create input and output variable like this.

6) Click OK. Now drag and drop an Assign activity and below invoke, give it appropriate name. Your composite will look like this

7) Now Double Click on you AssignOut put activity. And apply the following mapping and then click ok

8) Now your composite is ready to deploy. Deploy it and then test using EM console it will show the Master and detail table data.

In my next post i will show you how to insert data from one Database to another and i will also show how to use mediator in BPEL and about transformation.

Read More