A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. This sample shows how to use ref cursors as OUT parameter in a stored procedure or as return parameter in a function with WSO2 DSS. The sample is using oracle DB with DSS 3.5.1
SQL Scripts: To create table, insert data and create stored procedure & function.
Data Service
Following data service has two queries and associated operations.
Sample Requests
Operation GetCustomerDataAsOut
Request:
Response:
Operation GetCustomerDataAsReturn
Request:
Response:
SQL Scripts: To create table, insert data and create stored procedure & function.
CREATE TABLE customers (id NUMBER, name VARCHAR2(100), location VARCHAR2(100)); INSERT into customers (id, name, location) values (1, 'Anne', 'UK'); INSERT into customers (id, name, location) values (2, 'George', 'USA'); INSERT into customers (id, name, location) values (3, 'Peter', 'USA'); INSERT into customers (id, name, location) values (4, 'Will', 'NZ'); CREATE PROCEDURE getCustomerDetails(i_ID IN NUMBER, o_Customer_Data OUT SYS_REFCURSOR) IS BEGIN OPEN o_Customer_Data FOR SELECT * FROM customers WHERE id>i_ID; END getCustomerDetails; / CREATE FUNCTION returnCustomerDetails(i_ID IN NUMBER) RETURN SYS_REFCURSOR AS o_Customer_Data SYS_REFCURSOR; BEGIN OPEN o_Customer_Data FOR SELECT * FROM customers WHERE id>i_ID; return o_Customer_Data; END; /
Data Service
Following data service has two queries and associated operations.
- GetDataAsOut - Oracle ref cursor is used as out parameter of a stored procedure.
- GetDataAsReturn - Oracle ref cursor is used as return value of a function.
<data name="TestRefCursor" transports="http https local"> <config enableOData="false" id="TestDB"> <property name="driverClassName">oracle.jdbc.driver.OracleDriver</property> <property name="url">jdbc:oracle:thin:@localhost:1521/xe</property> <property name="username">system</property> <property name="password">oracle</property> </config> <query id=" " useConfig="TestDB"> <sql>call getCustomerDetails(?,?)</sql> <result element="CustomerData" rowName="Custmer"> <element column="id" name="CustomerID" xsdType="string"/> <element column="name" name="CustomerName" xsdType="string"/> </result> <param name="id" sqlType="INTEGER"/> <param name="data" sqlType="ORACLE_REF_CURSOR" type="OUT"/> </query> <query id="GetDataAsReturn" useConfig="TestDB"> <sql>{? = call returnCustomerDetails(?)}</sql> <result element="CustomerDataReturn" rowName="Custmer"> <element column="id" name="CustomerID" xsdType="string"/> <element column="name" name="CustomerName" xsdType="string"/> </result> <param name="data" ordinal="1" sqlType="ORACLE_REF_CURSOR" type="OUT"/> <param name="id" ordinal="2" sqlType="INTEGER"/> </query> <operation name="GetCustomerDataAsOut"> <call-query href="GetDataAsOut"> <with-param name="id" query-param="id"/> </call-query> </operation> <operation name="GetCustomerDataAsReturn"> <call-query href="GetDataAsReturn"> <with-param name="id" query-param="id"/> </call-query> </operation> </data>
Sample Requests
Operation GetCustomerDataAsOut
Request:
<body> <p:GetCustomerDataAsOut xmlns:p="http://ws.wso2.org/dataservice"> <!--Exactly 1 occurrence--> <p:id>2</p:id> </p:GetCustomerDataAsOut> </body>
Response:
<CustomerData xmlns="http://ws.wso2.org/dataservice"> <Custmer> <CustomerID>3</CustomerID> <CustomerName>Peter</CustomerName> </Custmer> <Custmer> <CustomerID>4</CustomerID> <CustomerName>Will</CustomerName> </Custmer> </CustomerData>
Operation GetCustomerDataAsReturn
Request:
<body> <p:GetCustomerDataAsReturn xmlns:p="http://ws.wso2.org/dataservice"> <!--Exactly 1 occurrence--> <p:id>2</p:id> </p:GetCustomerDataAsReturn> </body>
Response:
<CustomerDataReturn xmlns="http://ws.wso2.org/dataservice"> <Custmer> <CustomerID>3</CustomerID> <CustomerName>Peter</CustomerName> </Custmer> <Custmer> <CustomerID>4</CustomerID> <CustomerName>Will</CustomerName> </Custmer> </CustomerDataReturn>
No comments:
Post a Comment