How to use Oracle Ref Cursor in WSO2 DSS

Ref cursor in oracle is a data type and WSO2 DSS support this data type to be called. In this blog we will see that how ref cursor is called from DSS, for this I have created a table in oracle which holds the employee details and also created a stored procedure having a cursor into that and will get the employee details based on an input employee id.

Table: EMP_DETAILS

Now insert some data into this table so that from DSS service we can get employee detail based on employee id.

In this table I have inserted 3 records as above and now create a stored procedure which has ref cursor.

Stored Procedure: GETEMPLOYEE

create or replace PROCEDURE GETEMPLOYEE(

EMP_ID  IN VARCHAR2,

RESULT_CODE  OUT VARCHAR2,

EMPLOYEE_DETAILS_RESULT OUT SYS_REFCURSOR

) AS

BEGIN

  OPEN EMPLOYEE_DETAILS_RESULT FOR 

  SELECT * FROM EMP_DETAILS WHERE EMP_ID_N=EMP_ID;

  RESULT_CODE:='0';

  EXCEPTION WHEN OTHERS THEN

  RESULT_CODE:='-1';

END GETEMPLOYEE;

This procedure will be called from the DSS server. I am putting only DSS (.dbs) file code in below section. If you want to complete this section from the UI you can follow this blog which has the details that how DSS is created through UI.

DSS (.dbs) file:

 <data name="EmployeeDatailsService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">  
   <config id="EmployeeDS">  
    <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>  
    <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:SYSTEM/SYSTEM@localhost:1521/xe</property>  
    <property name="org.wso2.ws.dataservice.user">SYSTEM</property>  
    <property name="org.wso2.ws.dataservice.password">SYSTEM</property>  
    <property name="org.wso2.ws.dataservice.minpoolsize"></property>  
    <property name="org.wso2.ws.dataservice.maxpoolsize"></property>  
    <property name="org.wso2.ws.dataservice.validation_query"></property>  
   </config>  
   <query id="EmployeeDatailsQuery" useConfig="EmployeeDS">  
    <sql>call GETEMPLOYEE(?,?,?)</sql>  
        <result element="EmployeesDetails" defaultNamespace="http://shriwithjava.blogspot.co.nz/">    
            <element name="ResultCode" column="RESULT_CODE" xsdType="xs:string" />      
      <element name="EmpID" column="EMP_ID_N" xsdType="xs:string" />       
      <element name="Name" column="EMP_NAME_V" xsdType="xs:string" />       
      <element name="Address" column="EMP_ADD_V" xsdType="xs:string" />     
            <element name="Country" column="COUNTRY_V" xsdType="xs:string" />   
    </result>    
        <param name="EMP_ID" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1" />   
    <param name="RESULT_CODE" paramType="SCALAR" sqlType="STRING" type="OUT" ordinal="2" />   
       <param name="EMPLOYEE_DETAILS_RESULT" sqlType="ORACLE_REF_CURSOR" type="OUT" ordinal="3"/>  
   </query>  
   <operation name="getEmployeeDetails">  
    <description></description>  
    <call-query href="EmployeeDatailsQuery" >  
           <with-param name="EMP_ID" query-param="EMP_ID" />   
           </call-query>  
   </operation>  
 </data> 

Testing from Soap UI:

Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:shr="http://shriwithjava.blogspot.co.nz/">

   <soapenv:Header/>

   <soapenv:Body>

      <shr:getEmployeeDetails>

         <shr:EMP_ID>111</shr:EMP_ID>

      </shr:getEmployeeDetails>

   </soapenv:Body>

</soapenv:Envelope>

Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

   <soapenv:Body>

      <EmployeesDetails xmlns="http://shriwithjava.blogspot.co.nz/">

         <ResultCode>0</ResultCode>

         <EmpID>111</EmpID>

         <Name>SHRI</Name>

         <Address>RAEBARELI</Address>

         <Country>INDIA</Country>

      </EmployeesDetails>

   </soapenv:Body>

</soapenv:Envelope>