Working with Oracle Stored Functions and WSO2 DSS

When you want to invoke a Stored Procedure or Function on Oracle, you might want to use the WSO2 Data Services Server (DSS). The reason is that DSS has the capability to call stored procedures.

We can actually use Functions and Stored Procedures using WSO2 DSS. In this blog, we will look into the setup for a stored procedure with Oracle and WSO2 DSS.

Oracle Database

In order to test this, we should set up an Oracle environment. However, a complete setup of Oracle either on Linux or Windows including setting up the databases and tables would be a complete blog on its own. Since Oracle is an enterprise grade database system, installing it takes more time and effort and is often done by specialists. Oracle also owns MySQL which is easier to install. But do not underestimate MySQL, as it is also suitable for enterprises.

Anyways, we assume Oracle 11g2 to be installed and available. For this blog we have a Oracle instance on AWS and we use SQL Developer as a graphical front end to the database.

Stored procedure

In this blog, we will use a Stored Procedure that we create on Oracle. We will do a simple insert of a record in a table the database from the stored procedure.

These is of course simple example but it shows how it works.

Create a table

I’ve created a table that we will use for this blog. A simple table that stores rank, first and last name and time for a race. Normally, of course, time would be a different datatype but for now we use varchar to be quick. We will store a string of hh:mm:ss in it. .

Nr	Field	Type	Length
1	RANK	Numeric
2	FIRSTNAME	Varchar	20
3	LASTNAME	Varchar	50
4	FINISHTIME	Varchar	20

Let’s first create a stored procedure.

create or replace PROCEDURE INSERTFINISHER

(
FINISHEDAS IN NUMBER
, FINISHERFIRSTNAME IN VARCHAR2
, FINISHERLASTNAME IN VARCHAR2
, FINISHEDTIME IN VARCHAR2
) AS
BEGIN
INSERT INTO YENLO_TABLE (RANK, FIRSTNAME, LASTNAME, FINISHTIME) VALUES (FINISHEDAS, FINISHERFIRSTNAME, FINISHERLASTNAME, FINISHEDTIME);
END INSERTFINISHER;

As we can see Bob Beamon is now in the database. So the procedure works.

WSO2 DSS

In order to call the procedure and function we just created we need to use WSO2 Data Services Server. This product allows the calling of a stored procedure / function in a very simple way.

Download the WSO2 DSS (3.5.0) from the product page and install it on your machine. If you need help, read this blogpost on installing WSO2 Products.

Don’t forget to download the JDBC driver from Oracle. Subsequently install it in the [DSS=HOME]/repository/components/lib directory.

Start the DSS server using the [DSS-HOME]/bin/wso2server.sh or .bat file . Go to the carbon console which url is shown on the terminal Mgt Console URL : https://192.168.56.1:9443/carbon/

In a number of steps we will create a Data Service that will connect to the Oracle environment and that will have two queries and two operations, one for the Procedure and one for the function.

Step one Create Data Service

Fill in a name and namespace and click next.

We need to add a data source, all the connection details to the Oracle environment.

We fill in the information needed. Keep in mind that the information above is dummy information, there is no Oracle database at the above address with username and password as shown in the screenshot. You can test the connection to see if the connection details are correct. The User Name and Password are both tin the URL and as separate fields. This is an idiosyncracy of Oracle that requires this.

Adding a new query

Next we are going to add the procedure query.

We use a simple CALL statement with the four parameters (CALL INSERTFINISHER (?,?,?,?) with these defined in the input parameters. Each of these parameters is an ‘IN’ type parameter.

For example, the rank is defined as follows:

In some blogs online you see { } around the call. These are not necessary, it does work without them.

Add operations

We need to add operations to invoke the queries we just created.

We click finish to save what we just created.

In Deployed services we can actually see the YENLOTEST data service we just created.

Try It

But now we need to try it to see if it works. We submit a SOAP message to the Procedure. Since we did not define a response, we need to manually verify if the database now has two records.

First the visual check: As you can see Mickey Mouse came in second place.

So there you have it. A call to a stored procedure that will insert a record into a database. It is up to you to experiment with other types of stored procedures, now you know how to do it.

Functions

Using the same concept we can also call a function in WSO2 DSS. There is little difference between the two, other than the fact that a function returns a value.

So, I created this function in Oracle. It returns the number of records in the YENLO_TABLE.

Now we need a query in our Data Service. We will simple open up the version we created for the procedure and add a Query and Operation.

We create a Query with the {CALL ?:=HOWMANY()}.

The output variable is defined in the input mapping.

In the output mapping we define the field to give back the value.

Now we need to add an Operation as well.

We save the Data Service and go to the try it functionality. We use the FuncOps operation to retrieve the information from the database.

You can see that we have two records in the database using the function we just created.

Setting up Oracle as the WSO2 database

As an extra, consider migrating H2 to Oracle if Oracle is your company’s database of choice. Since we know how to connect to the Oracle environment it is a piece of cake. For more information look at this blog for the general explanation but use the specific oracle settings of course.

Oracle, in a sense, is just like the other databases supported by WSO2. To jog your memory, by default WSO2 uses the integrated H2 database for storage of, for instance, user and registry data. The only exception is WSO2 Identity Server that has an embedded LDAP for user data storage.

To change from H2 to Oracle in the WSO2 DSS is actually quite simple and requires four steps:

Download the JDBC driver;
Create a database,e.g. dssdatabase;
Run the sql script in [DSS-HOME]/dbscripts;
Change the master-datasources.xml in [DSS-HOME]/repository/conf/datasources;
Start DSS

If you have any questions about this blogpost contact us via the comments section of this blog. View also our WSO2 Tutorials, webinars or white papers for more technical information. Need support? We do deliver WSO2 Product Support, WSO2 Development Support, WSO2 Operational Support and WSO2 Training Programs.

Yenlo is the leading, global, multi-technology integration specialist in the field of API-management, Integration technology and Identity Management. Known for our strong focus on best-of-breed hybrid and cloud-based iPaaS technologies.
Yenlo is the product leader and multi-award winner in WSO2, Boomi, MuleSoft and Microsoft Azure technologies and offers best-of-breed solutions from multiple leading integration vendors.

With over 240+ experts in the API, integration, and Identity Access Management domain and over $35 million in annual revenue, Yenlo is one of the largest and best API-first and Cloud-first integration specialists worldwide.

About Rob Blaauboer

Rob is an Integration Consultant & WSO2 Trainer with more than twenty year experience. In addition to his work he is an active blogger working on a number of articles on the ‘Internet of Things’ and a WSO2 ‘Getting Started with …’ series (WSO2 tutorial) in which he talks about WSO2 components and their purpose especially aimed at non-technical readers. Rob is a WSO2 expert and official WSO2 trainer.

All blogs by Rob