How to use Oracle Ref Cursors with WSO2 Data Services Server?

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