fb
WSO2 Products 5 minutes

Working with Oracle Stored Procedures / Functions and WSO2 DSS

Rob Blaauboer
Rob Blaauboer
Integration Consultant & WSO2 Trainer
Stored procedures WSO2 DSS scaled
Scroll

Stored procedures WSO2 DSSWhen 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

Create a table - WSO2 DSS.png

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;

Run PLSQL - WSO2 DSS

Stored Procedure - in database

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 WSO2 DSS - main menuuse 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/

Log in using the admin / admin credentials and choose Create.

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

Create Data Server - WSO2 DSS

Fill in a name and namespace and click next.

Datasources - WSO2 DSS

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

New datasource WSO2 DSS

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.

Add new query WSO2 DSS

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.

Edit query wso2 DSS

For example, the rank is defined as follows:

Input mappings - wso2 dss

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

Queries WSO2 DSS

Add operations

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

Add new operation - wso2 dss

Operations WSO2 DSS

We click finish to save what we just created.

Deployed services - WSO2 DSS

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.

Yenlo Test SOAP Message WSo2 DSS

second place Database WSO2 DSS

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.

Create or replace function in Oracle

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()}.

Query WSO2 DSS - Oracle

The output variable is defined in the input mapping.

Input Mapping WSO2 DSS - Oracle

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

Result Output mapping WSO2 DSS

Now we need to add an Operation as well.

Operations WSO2 DSS - FuncOps

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

Records in Database WSO2 DSS - Oracle

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:

  1. Download the JDBC driver;
  2. Create a database,e.g. dssdatabase;
  3. Run the sql script in [DSS-HOME]/dbscripts;
  4. Change the master-datasources.xml in [DSS-HOME]/repository/conf/datasources;
  5. 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 SupportWSO2 Development SupportWSO2 Operational Support and WSO2 Training Programs.