Crave for Oracle: Oracle SOA 12c

Hi! In this post, I'm going to show you a sample requirement in Oracle SOA and how to implement it. So why late? Below is the sample requirement.

Note: This post is intended to newbies in SOA since I'm also a newbie😊. It may seem very easy for experienced professionals, don't blame me!!

Database will have Order Details table (Structure you can decide) & “Order Status” column will have value as “N” as soon as new entry is made in this table
On each entry in table, the SOA service should get invoked automatically (Order entries can happen in multiples)
Different SOA service should be available to fetch “Address Details” (Address Details should be stored in a “CSV” file format)
Fetch respective address details from “Address SOA Service” based on “City Code mentioned in Order details” from database
As per fetched address details, validation should be done that whether order can be dispatched to specific address or not (some addresses should not be available to dispatch)
If dispatching is not possible, error message should be written to database against that order
If dispatching is possible, for each State, the dispatching service should be different for each state where Dispatch requests should be “routed” to different state services based on “State Name” filter criteria
After receiving status as “Order Dispatched”, respective details should be updated against that order

First step in any requirement is to understand it, then dividing it into sub tasks and finish it. Isn't it?

So, now read the requirement carefully and understand it.

For this task, we need three services:

1. Main Service

2. Address Service

3. Routing Service

First we need to create a main service with db polling option that should fetch every new record that is inserted into the database table.

Secondly, an address service should be created where it takes city code as input and fetch address details from CSV file. To make the validation easy I'm going to use an extra column in my address file to show the dispatch status.

Thirdly, a routing service should be create to route the service if the order is dispatchable.

And finally, main service should invoke address service and routing service and connect these services together. So whenever a new record or multiple new records are entered into the table, service should get automatically invoked.

Here, I'm not going to show you by starting afresh because I already have the whole implementation ready but I'll show each and everything that made up as a whole..

My final Main Service implementation looks like below.

Okay, first things first. Lets start with PollData Service. Before that, create a table with order_details with your own structure but make sure you have a primary key, city code, state and status columns. In my case, I used below structure.

CREATE TABLE order_details_tbl

( ORDER_ID NUMBER PRIMARY KEY,

ORDER_NUMBER VARCHAR2(20),

ORDERED_ITEM VARCHAR2(40),

ORDERED_QTY NUMBER,

ORDER_AMOUNT NUMBER(9,2),

UOM VARCHAR2(20),

CITY_CODE VARCHAR2(20),

STATE VARCHAR2(20),

ERROR_MESSAGE VARCHAR2(200),

ORDER_STATUS VARCHAR2(10),

STATUS_MESSAGE VARCHAR2(200));

Step 1:

Create a SOA project. Drag and drop a db adapter to the Exposed Services swim lane. Give a suitable name and click Next.

Select your connection and click Next. If you haven't already set up the connection in weblogic, google it or click here to navigate to database adapter configuration.

Select the option "Poll for New or Changed Records in a Table". Click Next.

Select your table and click Next.

Click Next.

Select the option as logical delete and click Next.

Provide the column name where you want to provide the read value. You can also specify the values for Unread Value and Reserved Value if you want.

In the next screen, you'll see your query for polling SQL and update query after reading the data. Leave the polling frequency as default 5 seconds or change it. Here I want to poll for every second as I don't want to wait for 5 seconds after inserting values but initially, you can keep it as default and if it is working fine, you can always change it later. Leave all other options as default and click Next.

Here, we're not providing any parameters. So click Next.

Click Next.

Click on Finish button.

Now, drag and drop a BPEL with No Service and wire it to PollData db adapter. Edit the BPEL, drag and drop a Receive activity. Open the receive activity and select the "PollData" partner link under Interaction Type. Make sure to check "Create Instance" and create an input variable. Click Apply and then OK. Save all and close it. We'll come back to it later.

Step 2:

Now create an address service that takes city code as input and fetches address and dispatch status from csv and displays them in output. So, have a csv file with similar structure like below.

Create a SOA project and give it a suitable name. Now, drag and drop a file adapter to the External References swim lane. Here we're going to use the operation as 'Synchronous Read File' which is why I named it as below. Click Next.

Leave default interface and click Next.

Click Next.

Select Synchronous Read File as Operation Type and click Next.

Select the directory where you placed the Addresses csv file. If you'd like to archive the processed files, you can check that option and specify a directory where the archived files should go. Delete option is checked by default and greyed out, so we cannot change it now but can change it later. Here I don't want to delete the file from my directory because I'm going to use a loop in my service. So I changed it after setting up file adapter.

Specify the full name of your file along with extension.

In the next step, you need to click on Create schema icon which is next to magnifier icon to create a native schema. It will guide you through the process. Select delimited and provide a sample file csv file after which it will generate the XML.

Click Finish.

Now, go to .jca file of SyncRead file adapter. Change the property value for Delete from true to false and save it. You can check it now in the file adapter that the Delete option would be unchecked and greyed out.

Next step is to create a schema which will take CityCode as input and returns CityAddress, DispatchStatus as output.

Drag and drop a BPEL with synchronous template type that takes input and output from above schema. Leave the "Expose as a SOAP Service" checked. Wire your BPEL to file adapter. Now, your composite should look like below.

Edit the BPEL, drag and drop a For Each activity after Receive activity. We need For Each since we're having multiple records in csv file.

Open For Each and give a counter variable name.

Go to Counter Values tab and provide the initial value as 1 and final value as 5 since we're having five records in the address CSV file but ideally final value should not be hard coded. Suppose if some content is being added to file at intervals, we don't know how many lines are added every time. Since it is the sample assignment and I know the exact number of records in my file, I used the value which is constant. For variables records, should use count of that repeating variable.

Now, use an Invoke activity to invoke the file adapter. Create input and output variables. Anyway, we are not going to use these variables. But for some purpose, we need to create them. I'll tell you why.

Now, drag and drop an If activity just after invoke. Leave it for now and drag and drop an Assign activity inside If .

Now, go to the Variables section under For Each, right click on it to create variable. We need to create two variables - one for input and other for output. Both the variables should be of message type and select Invoke Input variable and Invoke Output variables types respectively. This is the reason we created invoke input and output variables.

Now go back to Invoke activity which is used to invoke file adapter. Select input and output variables as For Each input and output variables. This is the reason we created invoke variables to make them use for For Each variables. You can delete actual invoke variables now.

Now, go to your If condition and make sure you select the expression language as 'XPATH 1.0 in BPEL 2.0'. Give the condition as below. If the input city code matches with the city code in address file, then it should proceed.

Go to Assign activity, assign For Each output to your BPEL output as below.

With this, your address service is ready, test it by passing the city codes manually to make sure it is working fine.

Request:

Response:

Step 3:

Order Dispatch Service:

Next step is to create a routing service which will route the service based on State name filter. For this, we need to create a mediator service.

Create a SOA project with a suitable name as per your wish. Create an XSD schema that will take order_id and state as input and gives result as output. In my case, CityCode is optional. Its okay if it is not given as well.

Now create a Synchronous WSDL file based on this XSD using WSDL builder from gallery. Drag and drop a SOAP adapter to the Exposed Services side. Select the WSDL that is just created and click OK.

Drag and drop a Mediator with "Define Interface Later". Wire it to SOAP service on the left side.

Drag and drop a BPEL and name it as DTDL. Select the template type as Synchronous and uncheck "Expose as a SOAP Service". Select the input and output from the schema that we created. Create other two BPELs in the same way and give them different names as "Dwaraka" and "StarEnterprises". Then wire the mediator to these three BPELs.

By now, your service should look like this:

Edit the Mediator, under the DTDL filter condition, give the expression as below.

Click on the Assign activity under Assign Values, assign the mediator input to DTDL BPEL input.

Assign the output values using Assign activity under Synchronous Reply,

Do the same for Dwaraka and StarEnterprises with filter condition matching "Karnataka" and "Maharashtra'"respectively.

Now, edit the DTDL BPEL, drag and drop an assign activity and enter a custom message that the order has bee dispatched. Here, I concatenated Order id with the custom message to be more precise.

Do the same for other two BPELs. Add the custom message in the assign activity but make sure you name the correct courier service for each.

concat('Order ', $inputVariable.payload/ns1:orderid, ' dispatched via Dwaraka Courier Service')

concat('Order ', $inputVariable.payload/ns1:orderid, ' dispatched via Star Enterprises Courier Service')

Now, I want to update the same message in the database column for each service. For this, drag and drop a db adapter into the External References side. Lets do it for Telangana first. Give it a name.

In the next step, click Next. Select the operation as Insert or Update(Merge).

Select your table and select the column thatw e ant to update. Here, statusMessage is the column that I want to update. In order to update the same, I would like to pass orderId, cityCode and state. So I selected these columns.

Click Next and click Next until you Finish.

Now open your DTDL BPEL. Drag and drop an Invoke activity to invoke db adapter that is just created. Create an Input variable by clicking on green "+" icon.

Drag and drop an assign activity to assign orderId, state, cityCode and statusMessage as below.

So finally, your activities in DTDL BPEL should look like this:

Do the same thing for other two services. Use other two db adpaters and invoke then in BPELs and assign the variables as above.

With this we finished creating the Routing Service. The final composite should look like the first image in Step 3. Deploy the service to your server. You can test it manually to see if its working fine.

Request:

Response:

Status Message inserted into the table:

Step - 4:

Now its time to go back to our main service. Use a SOAP adapter to call the address service.

Wire it to Main BPEL and edit the BPEL to add invoke activity to invoke address service. Create input an output variables.

Drag and drop an Assign activity to assign cityCode from PollData to cityCode in Address Input.

Now, we want to check whether the address is dispatchable or nor based on the dispatch status that we get from address service. For this, add an If condition from the Structured Activities in Components pane. According to the requirement, if the address not dispatchable, it should send the error message to db. So, checking if the DispatchStatus is NO.

Now, we need to send the error message to the database. Go to the design vieqw of the composite and drag and drop a database adapter to the right side in the Exposed Services swim lane.

Select the operation as Merge.

Select the same table that we're using throughout the process and check only errorMessage column as we want to update only this column's data based on OrderId. Click Next until Finish.

Edit the BPEL, drag and drop an Invoke activity inside the If condition. Select the partner link as UpdateErrorMsg. Create Input variable. Click Apply and then OK.

Drag and drop an Assign activity just above Invoke activty and map the orderId from PollData to orderId in above Invoke activity input variable.

Add a custom message message to the errorMessage variable that the order is not dispatchable.

Now that we're done with first If condition, click on the + icon to add elseif condition.

If Dispatch_Status = YES, then only it should go inside this if condition. Open elseif and add the condition as below.

Drag and drop another If condition inside elseif to check if the state name is valid or not.

Add the condition in the nested If as below. As we have only three order dispatch services i.e., for Telangana, Karnataka and Maharashtra, we need to specify in the If condition that if the state name coming through this If condition matches three state names exactly, then only it should proceed.

If the state name is valid, it should call the Order Dispatch routing service. So go to design view of the Main composite and drag and drop a SOAP adapter and in the WSDL URL, paste the WSDL address of the Order Dispatch SOAP service and wire it to Main BPEL.

In the nested If, drag and drop an Invoke activity to invoke this Order dispatch SOAP adapter. Create Input and Output variables. Click Apply and OK.

Drag and drop an Assign activity to assign Order id, state and city code from PollData to Order Dispatch variables.

Now, lets come to the else part. If the state is not valid, it automatically goes to else block. So we need to send an error message to database that the state address is not dispatchable. Again we need to use a database adapter to update this message.

Select the table and select the Merge and check only state and statusMessage columns with Orderid already checked and disabled. Click Next until last step and click Finish.

Drag and drop an Invoke activity in else block of nested If condition. Select the partner link as the name of the above db adapter. create an input variable.

Drag and drop an Assign activity to map the variables.

Add the message to StatusMessage as below.

Now, your nested if and nested else blocks are ready. Go to the first level Else block where it will be executed if the dispatch status is neither YES or NO i.e., if the city code enters into the If block doesn't exist in the address CSV file, then the dispatch status will not exist either. In that case, it will be redirected to Else block. So here, we'll be updating the status message as address based on city code is not available since it is not present in the address file.

So, use a db adapter again to update the status message. and invoke it in the Else block and assign the custom message to StatusMessage column.

With this we come an end. Your final composite should look like below. Here, I added an extra catch All block to catch any errors that are unexpected. for this, I added another column in my table called FaultMessage and invoked the db to update the fault message column. You can add it if you'd like to.