We have already created a Data Service for the mysql database. Now we will use Stored Procedure in Oracle and call them in our data service.
Let’s first create database and Stored Procedure.
Create a database, in this example I have used database name is “shri”.
Enter Username/Password – shri/shri
Create a table with the name of “EMPLOYEE”
If you want to insert some values in this table, then use insert command.
Now in this service we have to use Stored Procedure, so let’s create a stored procedure with the name of “EMP_INFO_INSERT”.
Click “OK” button.
And write the stored procedure for insert values in employee table as given below.
create or replace
PROCEDURE EMP_INFO_INSERT(
p_emp_id IN EMPLOYEE.EMP_ID%TYPE,
p_emp_name IN EMPLOYEE.EMP_NAME%TYPE,
p_emp_add IN EMPLOYEE.EMP_ADD%TYPE,
p_department_id IN EMPLOYEE.DEPARTMENT_ID%TYPE,
p_designation_id IN EMPLOYEE.DESIGNATION_ID%type)
IS
BEGIN
INSERT INTO employee (EMP_ID, "EMP_NAME", "EMP_ADD", DEPARTMENT_ID, DESIGNATION_ID )
VALUES (p_emp_id, p_emp_name,p_emp_add, p_department_id, p_designation_id);
COMMIT;
END EMP_INFO_INSERT;
Compile it.
We are done with the database end, now need to create a service to call this stored procedure at DSS end.
DSS:-
Start the server from below location (double click on wso2server.bat file)
D:\...\wso2dataservices-2.5.1\bin\wso2server.bat
Server will be running on below address.
https://10.167.19.32:9443/carbon/admin/login.jsp
Enter Username/Password – admin/admin
You can see all the WebServices on clicking the of List link under the service tab.
Let’s create a new DSS service, click on create link under dataservice.
Enter “StoredProcedureExample” in dataservice textbox, click next.
Click Next.
In next page we need to add a Data Source.
Fill all required details as given below.
dataSource Id – OracleSPExample
DataSource Type – RDBMS
Database Engine - Generic
Driver Class - oracle.jdbc.driver.OracleDriver
JDBC URL - jdbc:oracle:thin:shri/shri@localhost:1521/xe
User Name – shri
Password - shri
Click on “Test Connection” Button, if connection is ok, will give successfully connected popup message otherwise give an error.
Click on “Save” button.
OracleSPExample data source is created. You can also edit or delete this data source.
If we need to create or add more data Source, repeat the above process again otherwise click “Next” button.
In next Page we have to add query which will fetch records from the database.
In this Page fill all required fields.
Query Id – selectEmployees
Data Source – OracleSPExample (It comes after creating data source in previous page)
SQl - select * from employee
Now we need give input or output mapping.
When we have to take output according to given input that we need to give here. In this example we are not giving any input criteria, just getting the output of the select query.
We have to define only output mapping here
In the “Result (Output Mapping)” block define below property.
Grouped by element – employees
Row name – employee
Row namespace - http://test.org
Click ‘Add Output Mapping‘.
Map all elements as defined in database table.
First element is added (EMP_ID).
EMP_NAME is added
Add EMP_ADD in output mapping
DEPARTMENT_ID is added
Last element in table is
DESIGNATION_ID click add button to add this element.
Click ‘Main Configuration’ button, click yes when popup is open.
Result (Output Mapping) will look like this.
Click Save button.
selectEmployees query has been added. Let’s add one more query to call stored procedure.
In this Page fill all required fields.
Query Id – employees
Data Source – OracleSPExample (It comes after creating data source in previous page)
SQl - call EMP_INFO_INSERT(?,?,?,?,?)
Now we need give input or output mapping.
When we have to take output according to given input that we need to give here. In this example we are passing input elements to add data in the table
Click Add New Input Mapping button
In above image map EMP_ID, it is numeric type in database so sql type ‘Numeric’ has been selected
Click add button
Map EMP_NAME
Click add button
Next is EMP_ADD
Click add button
DEPARTMENT_ID has been mapped
Click add button
Last is designation_id
Click Add button
Click main configuration button, click yes when popup is opened.
Input mapping will look like this (as below image)
On Clicking Save button below page should be shown.
Click Next button.
Click ‘Add New Operation’
Give addEmployee in Operation Name
Select Query Id – employees
Click Save button
Next page should be shown as below.
here we need to add one more operation
One for the adding record in the database (addEmployee)
Second is ‘selectEmployee’ which will fetch record from database.
Give Operation Name is selectEmployee
queryId – selectEmployees
click save button
Next page would look like this.
Click finish.
New service must be show in service list with the name of ‘StoredProcedureExample’.
Click ‘try this service’.
Click selectEmployee.
Whole record has been fetched from the database.
Now click addEmployee Operation.
Enter fields
Enter values and click ‘addEmployee’ button. Inserted record will be saved in the database, to show this value you can perform selectEmployee button again.
