How to use UDT (User Difined Types) within WSO2 Data Services Server ?

Other than the ordinal sql data types, WSO2 Data Services Server support to have UDT types(User Defined Types) in result of SQL query.

This post describes how to use Oracle UDT type result parameters in your data-service.

Step 1: Creating simple UDT type and a table.

Login to the oracle server and execute following sql commands to create new UDT type and table.

Creating UDT type:

CREATE OR REPLACE TYPE  phone_t AS OBJECT (
a_code   CHAR(3),
p_number CHAR(8));
/

Creating table that has field type of above created UDT (phone_t):

CREATE TABLE phone (
per_id    NUMBER(10),
per_phone phone_t);

You can verify the table structure by executing follwing two comands.

set describe depth all;
desc phone;

Insert some sample data to table:

insert into phone(per_id, per_phone) values(1, phone_t('SAM', '12345678'));
insert into phone(per_id, per_phone) values(1, phone_t('NOK', '87654321'));

Step 2: Creating data-service.

Here we are going to create a data-service that has an opertion to reteive all data from the phone table. The important thing to notice is, the phone table contains a UDT field called 'phone_t' and how we should map this into result elements when creating the data-service.

Clicking on the 'Create' link in left menu and enter a name to the Data service.

Next step is to enter details about the data-source which is using to create the data-service.

Next step is to create a query to extract information along with the structure of the response.

Following image shows how 'phone_t' column(UDT type) is mapped to output mappings in data-service.

Note that we should provide index of UDT attribute alone with the table column name to map the corresponding element in UDT. i.e in our sample UDT 'phone_t' index 0 maps to the attribute 'a_code' and index 1 maps to the attribute 'p_number'.
In 'phone' table 'per_phone' column type is set to UDT type 'phone_t'. So when we provide data-source column name as 'per_phone[0]', it will return the first element(i.e a_code) of phone_t object and 'per_phone[1]' will return the second element(i.e p_number) and so.

Final step is to add web service operation and finish the data-service creation.

The final dbs file will look as follows,

Click on "Try It" link to invoke the service and see the result.