How to call Oracle Stored Procedures from data services

An Oracle stored procedure is a program stored in an Oracle database which allows business logic to be embedded inside database as an API. You can expose the stored procedures as web services using wso2 data services server. This example takes you through creating a simple stored procedure in an Oracle DB and expose it as a data service using WSO2 Data Services Server.

Pre-requisites:

Download wso2 data services server from here

Oracle 10g or later

Step 1: Create and Populate sample database

First, open sqlplus shell and log in to DB as follows.

sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;

Enter password:

Connected.

We should create a user and grant him the necessary privileges as follows.

SQL> create user sample identified by sample account unlock;

User created.

SQL> grant connect to sample;

Grant succeeded.

SQL> grant create session, dba to sample;

Grant succeeded.

SQL> exit;

Now logged in as the new schema user as follows.

sqlplus sample/sample@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;

Enter password:

Connected.

SQL>

Create a table and insert data as follows.

SQL> create table employee (id number primary key, name varchar(100), address varchar(100));

SQL> insert into employee(id, name, address) values (1, 'charitha', 'colombo');

SQL> insert into employee(id, name, address) values (2, 'john', 'galle');

SQL> insert into employee(id, name, address) values (3, 'michel', 'otawa');

SQL> insert into employee(id, name, address) values (4, 'carl', 'dallas');

SQL> insert into employee(id, name, address) values (5, 'chanmira', 'colombo');

SQL> commit;

Now we are ready with oracle database and schema.

Lets write a simple stored procedure to insert more data to employee table.

Step 2: Writing a simple stored procedure

SQL> create or replace procedure addEmployeeSP(id number, name varchar, address varchar) is

2 begin

3 insert into employee (id, name, address) values (id, name, address);

4 end;

5 /

Step 3: Copy oracle jdbc driver

In order for wso2 data services server to communicate to oracle DB, we should download oracle jdbc driver (ojdbc14.jar) and copy in to DS_HOME/repository/components/lib directory.

Step 4: Create the data service through UI wizard

First, start wso2 data services server by running wso2server.sh {bat} which can be found at DS_HOME/bin

Then access management console through https://localhost:9443/carbon and log in using default admin credentials (admin/admin)

Click on Data Service --> Create in the left menu which will bring up data service creation wizard.

Enter a name for the data service and click on next. (For this example, I have specified "BlogExampleDataService" as the service name)

In Data Sources screen, click on Add new data source link and specify a data source ID and select RDBMS as the data source type. Then select Oracle as the Database engine and enter the db info as follows.

Driver Class = oracle.jdbc.driver.OracleDriver
JDBC URL = jdbc:oracle:thin:sample/sample@10.100.1.10:1521/orcl
User Name = sample
Password = sample



Click on "Test Connection" to see whether you can connect to oracle server correctly. Then click on save. Now we have created a data source hence we can proceed through the wizard.

Click on Next to move to query definition page. Select Add New Query to create a new query for our data service.

Enter a query ID (ex:- employees) and select the data source we just created from the Data Source drop down.

We can specify our SQL statement in the SQL text area. We need to call the oracle stored procedure which has been created earlier. You can enter "call addEmployeeSP(?,?,?)" as the query to call oracle stored procedure. ? denotes the input parameters which should be passed to the stored procedure.



Next, click on Add new input mapping and add three new input parameters since our query accepts 3 different parameters.



After adding all 3 input parameters, the query will be looked as follows.



We can save the query now. Our stored procedure inserts employee records in to the table therefore it does not return anything.
Because of that, the output mappings are not required for the query.

In order to check whether the records are correctly added to the database, lets create another query, selectEmployees as shown in the following screen.



Now, we have created both queries. Lets add operations which are necessary to run these queries.

Click Next in the Queries screen to proceed through the wizard which will bring up Operations page. Click on Add New Operation link. Specify operation name, addEmployee and select Query ID, employees.



Similarly, add an operation for selectEmployee query and name it as selectAllEmployees.

Click on Finish to deploy the data service. Once it is deployed, the service will be shown in the service list. Click on Try this service link to test the service.
In there, you will find two operations, addEmployee and selectAllEmployees. First invoke addEmployee operation by specifying id, name and address.
id=6
name=bloguser
address=notown

Now, if you invoke selectAllEmployees operation, you will see that the employee table has been updated with a new record.



Thats all! Drop me a mail or post your question at wso2.org forum if you have any questions about WSO2 Data Services Server.