Useful Excel functions for a SOA resource

VLOOKUP Function:

One simple requirement for VLOOKUP function is to compare two sheet and find out the matching rows.

For example here we have two tables of Order ID’s and we want to match them (get matching and not matching ID’s).

We can copy both the list in same or to be more clear in different sheets.

                                            

On an available cell, we need to write the VLOOKUP function as below, So this lookups for a value in

 an array range and if found returns a column of that matched row.

Range lookup = true – approx. match will be returned

Range lookup = false – exact match will be returned

range_lookup   (optional)

A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: ·         TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. ·         FALSE searches for the exact value in the first column.

 

So below function fulfils our purpose.

 

BPEL correlation and Message Aggregation

How does BPEL identify which response is for which request?

We all know that there are two major type of processing synchronous and asynchronous and for synchronous processing it uses same TCP connection or in simpler terms single port. So there is no need of correlation for synchronous process.

For Asynchronous processing, BPEL process will usually correlate message using "WS-Addressing", it’s something like web session cookies, which happens internally and we do not need to worry about correlating response messages.

WS-Addressing

The basic question every beginner has is how an asynchronous response gets mapped to the correct instance out of many waiting instances. The answer is, whenever asynchronous service is invoked, Oracle BPM adds a message UID in WS-Addressing headers that flows with SOAP packets, which are used internally by BPEL server to correlate the request/response.

So when does we need manual Correlation?

Below are the scenarios when we will require correlation:

• When external web service doesn’t support WS-Addressing
• When the message travels through several services and the response is solicited by the initial service from the last service directly. For example, Request path is A > B > C and response comes to A directly from C, i.e. C > A
• When receiving unsolicited messages from another system.
• When communicating via files, i.e. When BPEL instance is expecting messages from a third party.

What is correlation?

It is a BPEL technique called correlation set to match or correlate the messages based on content of the message.

It is a collection of properties used by the BPEL process service engine to allow custom correlation.

Before starting with our example we need to understand in which activities do we need correlation. Well the answer is quite self-explanatory, we need correlation whenever data is going out or coming in to the BPEL process, i.e.

• Receive activity
• Reply activity
• Invoke activity
• onMessage branch
• onEvent branch

I will first give the scenario of our example:

you can download the sample from here.
we will have two receive activities in our BPEL process,
One receive will take input as Country Name and Language
Other receive will take input as Country Name and Capital.
The response will have Country Name, Language and Capital.

SOA12C: Getting started – Hello World project OSB

One of the major change in 12C is the integration of OSB IDE within Jdeveloper and hence development and testing of SOA as well as OSB can now be done from within Jdeveloper without the need of installing Eclipse & OSB.

In our previous post we did a Hello World project in SOA, If you have not done that, first do SOA part as in this example we will be calling SOA service, now let’s get started with OSB.

In this sample we will call the Hello World project we created in SOA through business service.

you can download this sample from here.

Let’s first create a new application for service bus:

Name your project accordingly:

SOA12C: Getting started – Hello World project SOA

In the previous post I showed the installationof Oracle SOA in windows 7 machine and to configure integrated domain.

So let’s start with our sample Hello World Project:
you can download this sample from here.

Create a new SOA application:

Enter the Application name and directory location, for the first time you will have to create myworks directory for ex. For my case below is the location I have specified for this project:

SOA 12C installation in Ubuntu

In this post we will see step by step installation process on Ubuntu platform. If you want to see if for Windows, refer my other post.

First step: Install Oracle JDK, 

you can use below commands to install JDK7:

Open terminal and run below commands:

1.             sudo add-apt-repository ppa:webupd8team/java 

2.             sudo apt-get update 

3.             sudo apt-get install oracle-java7-installer

Confirm the Java version:  

Second step: Install Soa Suite 12C

Download the Generic version of installable. From OTN downloads.

go to the downloaded directory, extract the installable and run below command:

java -jar fmw_12.1.3.0.0_soa_quickstart.jar

SOA 12C installation Part 2 - Create Integrated weblogic server default domain

In this post, we see how to create Integrated weblogic server default domain.

This is the second post for the series SOA 12C step by step installation,

So before proceeding with this post you need to read first part.

To create the default domain, go to Window--> Application Server:

Right click on IntegratedWebLogicServer and click on Create Default Domain.

 Enter the domain password and accept defaults.

This domain creation will take some time to finish.

Once you are done with installation and configuration, its time to Get started with 12C development.

SOA 12C installation Part 1 - step by step installation

In this blog we will see how to install SOA Suite 12 C on windows 7.

If you want to see installation steps on Ubuntu platform, refer my other post.

Step 1:

The first step is downloading the SOA 12C installable from Oracle OTN. Enter your Oracle Technology Network account, if you don’t have one you’ll need to create an account.

It includes Oracle BPEL Process Manager, Oracle Human Workflow, Oracle Business Rules, Oracle Mediator, Oracle Service Bus, Technology Adapters Oracle Enterprise Scheduler, SOA Spring Component, Enterprise Manager Fusion Middleware Control, Oracle JDeveloper with SOA IDE extensions and an integrated WebLogic Server and Java DB.

http://www.oracle.com/technetwork/middleware/soasuite/downloads/index.html

Step 2:

While the SOA suite is being downloaded, we can check on JDK versions. A recent version of JDK is required, I am using jdk-7u79-windows-x64.exe, I actually tried with JDK 8 but it SOA didn’t support that version so I had to revert to JDK7. If you don’t have java installed, download and install from below location:

http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

Step 3:

Install Java and set your Java_HOME environment variable to the installed location, for example, I have installed Java at C:\Oracle\12C\jdk.1.7.0_79

AQ and SOA - Part2 - Posting message to AQ queue from SOA

In the first part we created an AQ queue and posted a sample message into the queue.
In this part we will develop a soa composite to post message into AQ queue.

Before we start creating SOA composite, we first need to configure server resource to access AQ from SOA. We will need a datasource which connects to the database and an AQ outbound connection pool which we will use in our project.
we will create a bpel with exposed webservice to post message to AQ.
In our previous post while object for the queue we gave employee_id, employee_number

create or replace type employee_type as object(

employee_id number,

employee_number varchar2(60),

employee_name varchar2(60)

);

Let’s create a new application for this example:

Create an empty soa project:

Now first we need to create a schema file, as we are going to expose our BPEL as a web service, we will need to enter our input there which will later on be inserted into AQ.

Click on New and select XML Schema:

AQ and SOA - Part1 - Creation of AQ queue

Oracle AQ is a database queuing system and is implemented in database tables. So the first thing we need is database access.

Requirements:

If you have the database schema/user details in which you are going to create the queue then skip below step, otherwise, 

Create a user:

sqlplus sys@localhost:1521/xe as sysdba

create user AQ_SOA_USER identified by oracle;

Grant priviledge to create tables and queues:

grant create session, resource, AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE to AQ_SOA_USER;

grant execute on DBMS_AQADM to AQ_SOA_USER; -- enque/dequeue priviledge to the user 

grant execute on DBMS_AQ to AQ_SOA_USER;  -- to enable compilation of a PL/SQL procedure 

once we have the user details, login to database using above user(AQ_SOA_USER), you can use SQLDeveloper/Jdeveloper or any other tool available.

Create Object

create or replace type employee_type as object(

employee_id number,

employee_number varchar2(60),

employee_name varchar2(60)

);

Create a queue table

begin

DBMS_AQADM.CREATE_QUEUE_TABLE (

queue_table => 'AQ_SOA_USER.employee_queue_table',

queue_payload_type => 'AQ_SOA_USER.employee_type');

end;

Create a queue

BEGIN

DBMS_AQADM.CREATE_QUEUE (

queue_name =>'AQ_SOA_USER.employee_queue',

queue_table=>'AQ_SOA_USER.employee_queue_table');

END;

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;

Start the queue

exec DBMS_AQADM.START_QUEUE('AQ_SOA_USER.employee_queue');

ORA-01653: unable to extend table DEV_MDS.MDS_ATTRIBUTES by 1024 in tablespace

Sometimes we get below error while deploying our bpel to server.
To solve this either contact your administrator requesting to extend the tablespace assigned to respective schema or if you have access of DB, perform below steps:

Problem:

ORA-01653: unable to extend table DEV_MDS.MDS_ATTRIBUTES by 1024 in tablespace DEV_MDS

How to fix:

C:\Users\admin>sqlplus sys/welcome1@XE as sysdba

SQL> alter tablespace DEV_MDS add datafile 'C:\ORACLEXE\ORADATA\XE\DEV_MDS2.DBF' size 1000M

Rotate log files

By default, when you start a WebLogic Server instance in development mode, the server automatically renames (rotates) its local server log file as SERVER_NAME.log00001, where SERVER_NAME is the name of the server. For the remainder of the server session, log messages accumulate in SERVER_NAME.log until the file grows to a size of 500 kilobytes.
Each time the server log file reaches this size, the server renames the log file using the syntax SERVER_NAME.lognnnnn, and creates a new SERVER_NAME.log to store new messages. By default, the rotated log files are numbered in order of creation using the syntax SERVER_NAME.lognnnnn, where SERVER_NAME is the name configured for the log file. You can configure a server instance to include a time and date stamp in the file name of rotated log files.
By default, when you start a server instance in production mode, the server rotates its local log file whenever the file grows to 5000 kilobytes in size. It does not rotate the local server log file when you start the server. For more information about changing the mode in which a server starts, see Change to production mode.
You can change these default settings for log file rotation. For example, you can change the file size at which the server rotates the log file or you can configure a server to rotate log files based on a time interval. You can also specify the maximum number of rotated files that can accumulate. After the number of log files reaches this number, subsequent file rotations overwrite the oldest log file.
Note: WebLogic Server sets a threshold size limit of 500 MB before it forces a hard rotation to prevent excessive log file growth.

call-template vs. apply-template

call-template:

<xsl:call-template name="somename"/>  is like calling a function in a traditional programming language.

we can define a function in XSLT like:
 
and then we can call this function using <xsl:call-template name="printname"/>,
 for ex.:

apply-templates: 

<xsl:apply-template> tag signals the XSLT processor to find the appropriate template to apply, based on the type and context of each selected node.
for example - below is our xml file:

and below is the xslt we are operating on above xml:


the output of this xslt will be:


or in html format:

Testing JSR-160 Runtime ... failed

Sometimes when we configure Jdeveloper for first time to some server, we get  below error:

Testing JSR-160 Runtime ... failed.
Cannot establish connection.
Testing JSR-160 DomainRuntime ... skipped.
Testing JSR-88 ... skipped.
Testing JSR-88-LOCAL ... skipped.
Testing JNDI ... skipped.
Testing JSR-160 Edit ... skipped.
Testing HTTP ... success.
Testing Server MBeans Model ... skipped.

One solution  which you will find everywhere over internet is to remove any proxy in Jdeveloper, or have the same proxy as in browser:
Goto Tools -> Preferences in jdeveloper and type "proxy" in the search box, the proxy setting will appear. Uncheck the proxy checkbox , save the setting and restart Jdeveloper.

However in some cases this is not the issue, if above does not work do below:
update your host file and add:

Adminserver-IP   Hostname

now test your connection, your test should be successful. 
Let me know how it goes :)