How to use nested UDTs with WSO2 DSS

WSO2 Data Services Server(DSS) is a platform for integrating data stores, creating composite data views and hosting data in different sources such as REST style web resources.

This blog guides you through the process of extracting the data using a data services when nested User Defined Types (UDT) used in a function.

Lets take the following oracle package that returns a nested UDT. When a nested UDT (A UDT that use standard data types and other UDT in it) exists in the oracle package, oracle package should be written in a way that it returns a single ref cursor, as DSS do not support nested UDTs out of the box.

Lets take the following oracle package that includes a nested UDT called 'dType4'. In this example I have used Oracle DUAL Table to represent the results of multiple types included in the 'dType4'.

Sample Oracle Package

create or replace TYPE dType1 IS Object (City VARCHAR2(100 CHAR) ,Country VARCHAR2(2000 CHAR));
/
create or replace TYPE dType2 IS TABLE OF VARCHAR2(1000);
/
create or replace TYPE dType3 IS TABLE OF dType1;
/
create or replace TYPE dType4 is Object(
Region VARCHAR2(50),
CountryDetails dType3,
Currency dType2);
/

create or replace PACKAGE myPackage IS
FUNCTION getData RETURN sys_refcursor;
end myPackage;
/
create or replace PACKAGE Body myPackage as FUNCTION getData
RETURN SYS_REFCURSOR is
    tt  dType4;
    t3  dType3;
    t1  dType1;
    t11 dType1;
    t2  dType2;
    cur sys_refcursor;
  begin
    t1  := dType1('Colombo', 'Sri Lanka');
    t11 := dType1('Delihi', 'India');
    t2  := dType2('Sri Lankan Rupee', 'Indian Rupee');
    t3  := dType3(t1, t11);
    tt  := dType4('Asia continent', t3, t2);
    open cur for
      SELECT tt.Region, tt.CountryDetails, tt.Currency from dual;
    return cur;
  end;
end myPackage;
/

Lets see how we can access this Oracle package using the WSO2 Data Services Server.

Creating the Data Service

1. Download WSO2 Data Services Server
2. Start the server and go to "Create DataService" option
3. Create a data service using given sample data source.

In this data service I have created an input mapping to get the results of the oracle cursor using 'ORACLE_REF_CURSOR' sql type. The given output mapping is used to present the  results returned by the oracle package.

<data name="NestedUDT" transports="http https local">
   <config enableOData="false" id="oracleds">
      <property name="driverClassName">oracle.jdbc.driver.OracleDriver</property>
      <property name="url">jdbc:oracle:thin:@XXXX</property>
      <property name="username">XXX</property>
      <property name="password">XXX</property>
   </config>
   <query id="qDetails" useConfig="oracleds">
      <sql>{call ?:=mypackage.getData()}</sql>
      <result element="MYDetailResponse" rowName="Details" useColumnNumbers="true">
         <element column="1" name="Region" xsdType="string"/>
         <element arrayName="myarray" column="2" name="CountryDetails" xsdType="string"/>
         <element column="3" name="Currency" xsdType="string"/>
      </result>
      <param name="cur" ordinal="1" sqlType="ORACLE_REF_CURSOR" type="OUT"/>
   </query>
   <resource method="GET" path="data">
      <call-query href="qDetails"/>
   </resource>
</data>

Response of the data service invocation is as follows

<MYDetailResponse xmlns="http://ws.wso2.org/dataservice">
   <Details>
      <Region>Asia continent</Region>
      <CountryDetails>{Colombo,Sri Lanka}</CountryDetails>
      <CountryDetails>{Delihi,India}</CountryDetails>
      <Currency>Sri Lankan RupeeIndian Rupee</Currency>
   </Details>
</MYDetailResponse>