WSO2 DSS: Issue in Creating Data services using an oracle Stored Procedure with Ref Cursor and other Scalar out parameter

Question

I am learning WSO2 DSS. I am was trying to implement one of my use case using DSS and Oracle Stored Procedure.

Oracle Stored procedure i am using has one input parameter and two scalar out parameter while one ref cursor.

Database Script

CREATE TABLE DSS_DEPT
    (
        DEPT_ID   VARCHAR2(10),
        DEPT_NAME VARCHAR2(10)
    );

CREATE TABLE DSS_EMP
    (
        EMP_ID VARCHAR2(10),
        FNAME  VARCHAR2(10),
        LNAME  VARCHAR2(10),
        TITLE  VARCHAR2(10)
    );

CREATE TABLE DSS_EMP_DEPT
    (
        DEPT_ID VARCHAR2(10),
        EMP_ID  VARCHAR2(10)
    );

CREATE OR REPLACE PROCEDURE Get_emp_data(
        p_EMP_ID IN DSS_EMP.EMP_ID%type,
        p_emp_Fname OUT DSS_EMP.FNAME%type,
        p_emp_Lname OUT DSS_EMP.LNAME%type,
        P_DEP_RSET OUT sys_refcursor )
IS
BEGIN

    SELECT FNAME,
        LNAME
    INTO p_emp_Fname ,
        p_emp_Lname
    FROM DSS_EMP
    WHERE EMP_ID = p_EMP_ID;

    OPEN P_DEP_RSET FOR SELECT DEPT_ID FROM DSS_EMP_DEPT WHERE EMP_ID = p_EMP_ID;

END;
/

Data Insert Script

Insert into DSS_DEPT (DEPT_ID,DEPT_NAME) values ('INV','Inventory');
Insert into DSS_DEPT (DEPT_ID,DEPT_NAME) values ('SAL','Sales');


Insert into DSS_EMP (EMP_ID,FNAME,LNAME,TITLE) values ('2','Alpesh','B','SE');
Insert into DSS_EMP (EMP_ID,FNAME,LNAME,TITLE) values ('1','Alps','B','SE');

Insert into DSS_EMP_DEPT (DEPT_ID,EMP_ID) values ('SAL','1');
Insert into DSS_EMP_DEPT (DEPT_ID,EMP_ID) values ('INV','2');
Insert into DSS_EMP_DEPT (DEPT_ID,EMP_ID) values ('SAL','2');
commit;

DSS Configuration:

<data name="MyDSSTest" transports="http https local">
   <description>Testing WSO2 DSS with Oracle</description>
   <config enableOData="false" id="DBTEST">
      <property name="carbon_datasource_name">DBTEST</property>
   </config>
   <query id="GetEmpData_qry" useConfig="EDBTEST">
      <sql>call Get_emp_data (:P_EMP_ID ,:P_EMP_FNAME,:P_EMP_LNAME,:P_DEP_RSET)</sql>
      <result element="Employees" rowName="Employee">
         <element name="P_EMP_ID" query-param="P_EMP_ID" xsdType="string"/>
         <element column="P_EMP_FNAME" name="P_EMP_FNAME" xsdType="string"/>
         <element column="P_EMP_LNAME" name="P_EMP_LNAME" xsdType="string"/>
         <element column="DEPT_ID" name="DEPT_ID" xsdType="string"/>
      </result>
      <param name="P_EMP_ID" sqlType="STRING"/>
      <param name="P_EMP_FNAME" sqlType="STRING" type="OUT"/>
      <param name="P_EMP_LNAME" sqlType="STRING" type="OUT"/>
      <param name="P_DEP_RSET" sqlType="ORACLE_REF_CURSOR" type="OUT"/>
   </query>
   <operation name="GetEmpData">
      <call-query href="GetEmpData_qry">
         <with-param name="P_EMP_ID" query-param="P_EMP_ID"/>
      </call-query>
   </operation>
   <resource method="GET" path="GetEmpData/{P_EMP_ID}">
      <call-query href="GetEmpData_qry">
         <with-param name="P_EMP_ID" query-param="P_EMP_ID"/>
      </call-query>
   </resource>
</data>

Result of REST Call

<Employees>
  <Employee>
    <P_EMP_ID>2</P_EMP_ID>
    <P_EMP_FNAME>Alpesh2</P_EMP_FNAME>
    <P_EMP_LNAME>Bhalodia2</P_EMP_LNAME>
    <DEPT_ID>INV</DEPT_ID>
  </Employee>
  <Employee>
    <P_EMP_ID>2</P_EMP_ID>
    <P_EMP_FNAME>Alpesh2</P_EMP_FNAME>
    <P_EMP_LNAME>Bhalodia2</P_EMP_LNAME>
    <DEPT_ID>SAL</DEPT_ID>
  </Employee>
</Employees>

Result Format I want

<Employees>
  <Employee>
    <P_EMP_ID>2</P_EMP_ID>
    <P_EMP_FNAME>Alpesh2</P_EMP_FNAME>
    <P_EMP_LNAME>Bhalodia2</P_EMP_LNAME>
    <DEPTS>
      <DEPT_ID>INV</DEPT_ID>
      <DEPT_ID>SAL</DEPT_ID>
    </DEPTS>
  </Employee>
</Employees>

I want the Department values from cursor to exported as Complex element as show above. I tried all different type of combination in setting up output mapping in DSS but nothing worked.

I know this can be achieved by using XSLT on top of Results but I don't want to do that.

Am I doing anything wrong in setting Output mapping? how to Export Cursor result as Array in DSS?

Answer 1

This is the expected behavior. For each DEPT_ID in there will be an Employee row in the result set.