How to use Oracle Ref Cursors with WSO2 Data Services Server?
- Kalpanie Wanigaratne
- - WSO2
Contents
- Introduction
- Getting Started
- Creating and populating the sample database.
- Creating the data service
- Creating the data source
- Creating the Query
- Creating the operation.
- Deployed Services
- Invoke the service.
- Conclusion
Introduction
General static cursor can only be used to get data which associates with one SELECT statement and it cannot be used dynamically. Therefore if you need to fetch data associated with different SELECT statements at different times you cannot use a general explicit cursor. The answer for such a scenario is REF cursors. In the case of a normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself. But in REF cursor, the cursor declartion is not associated with any SQL query and it is associated with a query at a later stage. This brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time) programatically. REF cursors also provide the feature of passing parameters. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures functions packages etc).
WSO2 Data Services Server caters to the feature of supporting REF cursors. This document will guide you in writing a REF cursor and to use it within WSO2 Data Services Server.
Getting Started
The following are the prerequisites to continue with the tutorial.
1) Download WSO2 Data Services Server version 2.2.0 from https://wso2.org/downloads/data-services-server
2) Running Oracle instance
3) Copy Oracle JDBC driver to DS_HOME/components/lib
4) Start WSO2 Data Services Server ( Run DS_HOME/wso2server.bat | wso2server.sh )
5) Open a web browser and navigate to https://localhost:9443/carbon
6) If you have not changed the default Admin user credentials you can use it to login to the system, otherwise you can use a created user name, and a password to login.
Step 1 : Creating and populating the sample database
Before we start on create the data services we will begin with creating the database with a ref cursor.
Let's create a table called 'dept' and insert values to the table.
/* create and populate dept table */ DROP TABLE dept / CREATE TABLE dept ( dept_id NUMBER , name VARCHAR2(100) , location VARCHAR2(100) ) / INSERT INTO dept VALUES (100 , 'ACCOUNTING' , 'BUTLER, PA'); INSERT INTO dept VALUES (101 , 'RESEARCH' , 'DALLAS, TX');
INSERT INTO dept VALUES (102 , 'SALES' , 'CHICAGO, IL');
INSERT INTO dept VALUES (103 , 'OPERATIONS' , 'BOSTON, MA');
INSERT INTO dept VALUES (104 , 'IT' , 'PITTSBURGH, PA');
INSERT INTO dept VALUES (105 , 'ENGINEERING' , 'WEXFORD, PA');
INSERT INTO dept VALUES (106 , 'QA' , 'WEXFORD, PA');
INSERT INTO dept VALUES (107 , 'PROCESSING' , 'NEW YORK, NY');
INSERT INTO dept VALUES (108 , 'CUSTOMER SUPPORT' , 'TRANSFER, PA'); INSERT INTO dept VALUES (109 , 'HQ' , 'WEXFORD, PA');
INSERT INTO dept VALUES (110 , 'PRODUCTION SUPPORT' , 'MONTEREY, CA');
INSERT INTO dept VALUES (111 , 'DOCUMENTATION' , 'WEXFORD, PA');
INSERT INTO dept VALUES (112 , 'HELP DESK' , 'GREENVILLE, PA');
/ commit; / /* CREATE PACKAGE "ref_cursor_package" */ CREATE OR REPLACE PACKAGE ref_cursor_package AS TYPE t_ref_cursor IS REF CURSOR; FUNCTION get_dept_ref_cursor(p_dept_id INTEGER) RETURN t_ref_cursor; END; /
Following package will retrieve the dept_id, name and location from dept table and the reference to the cursor (REF cursor ) will be returned by the function.
CREATE OR REPLACE PACKAGE BODY ref_cursor_package AS FUNCTION get_dept_ref_cursor (p_dept_id INTEGER) RETURN t_ref_cursor IS dept_ref_cursor t_ref_cursor; BEGIN OPEN dept_ref_cursor FOR SELECT dept_id, name, location FROM dept WHERE dept_id > p_dept_id ORDER BY dept_id; RETURN dept_ref_cursor; END get_dept_ref_cursor; END ref_cursor_package; /
'get_dept_ref_cursor' is a simple function which will get the department id from the user as an input parameter and will return the dept_id, name and the location of the departments where the department id's greater than the input value. The values would return as a ref_cursor.
We will execute the function in sqlplus console like showing in below figure.
Figure 1 : Executing the Oracle function
Step 2 : Creating the data service
We can proceed on creating the data service since we are done with creating the database.
Let's start on creating the data service by clicking on 'Create' link under Services/Add/Data Service. We'll name the service as 'OracleRefCursorSample' as shown below.
Figure 2 : Creating the data service by naming the service.
Step 3 : Creating the data source
Once you create the data service click on next and then on 'Add New Data Source'. This page is to enter the details of the data source which we are using to create the data service. Let's name the data source as 'oracle_ds'. The following image shows the Add Data Source page with the entered data.
Click on 'Save' button once you have filled the data source information. You can click on 'Test Connection' to test the database connection.
Step 4: Creating the Query
Once you have saved the data source, click on 'Next' button to create the query. Start creating the query by naming the query. Let's name it as 'get_dept'. Select the created data source from the drop down list. Enter the query to call the function. Lets enter the query as {? = call ref_cursor_package.get_dept_ref_cursor(?)}
Now click on 'Add Input Mapping' button to enter the parameters. Since 'get_dept_ref_cursor ' function accepts dept_id as INPUT and returns the REF cursor we need to specify them as input mappings.
Let's enter the following details.
1) Mapping Name : dept_id
SQL type : INTEGER
IN/OUT type : IN
Ordinal :2
2) Mapping Name : dept_info
SQL type : ORACLE_REF_CURSOR
IN/OUT type : OUT
Ordinal :1
Figure 4 : Adding Input Mappings
Once you have entered two input parameters click on 'Main Configuration' to go back to the main page. Then we'll enter the details to create the output result set. Group By Element Name will help you to define a result set name. We will name this as Departments for this scenario and the row we will name as 'department'.
Click on 'Add Output Mapping' to enter the output elements. Let's add name, dept_id and location which comes in the result set as output parameters as shown in the following figure.
Figure 5 : Creating the query
After adding the relevant details click on 'Save' and 'Next' to create the operation.
Step 5: Creating the operation
Let's name the operation as 'GetDeptInfo' and select the defined query id (get_dept) in the previous step. Click on 'Save' and then 'Finish'.
Step 6: Deployed Services
Once you click on finish, the data service will deploy and it will list down under List of Deployed services. Following figure shows the deployed 'OracleRefCursorSample' deployed service.
Step 7: Invoke the service
Click on 'Try It' link on the deployed service to invoke and test the service. Lets enter 110 as the dept_id, it will list down all the other departments which are greater than 110, which is given by the function.
Figure 8 : Invoked Service
Created data service (OracleRefCursorSample.dbs) will look as below.
Conclusion
This tutorial speaks about what is an Oracle REF cursor and how you can use the REF cursors within WSO2 Data Services Server.
Author
Kalpanie Wanigaratne, WSO2 Inc. kalpanie at wso2 dot com