Saturday, February 11, 2012

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.

5 comments:

  1. Hi dinusha , thanks for your blog, it's very helpful for me ! I have question about the UDT.. I was wondering if the UDT works if there are defined at level package like :

    create or replace
    PACKAGE PCKTYPE AS

    type numerostel is table of number(1) index by binary_integer;

    END PCKTYPE;

    I've tried to use my UDT as you explain in the post but it's not working .. I've sql error:
    java.sql.SQLException: invalid name pattern: : MY_SCHEMA.PCKTYPE.NUMEROSTEL

    ReplyDelete
  2. Hi Dinusha,

    I have one Query
    Here i am sending the my GIT URL which contain my Query

    https://gist.github.com/anonymous/5300686

    Could you please help me.
    Thanks in Advance
    Anil

    Hi Lakmali,

    ReplyDelete
  3. Hi Dinusha
    I am trying call Stored procedure that has UDT , using WSO2DSS.
    Version : MAC OS X - 10.7.5 with WSO2 ESB 4.0.3 having installed feature of Data Service feature - 3.2.3

    "

    DataSource


    {call PRO_ALERT_DISP_FLOAT(:IN_DS_ID,:IN_PRIORITY,:IN_TIME_OFFSET,:IO_STATUS,:out_display_list,:IO_IS_ADMIN_GENERIC)}

    60000
    forward
    1000









































    "
    All I get is
    "[2013-04-26 15:53:52,183] ERROR - SQLQuery Conversion to String failed
    java.sql.SQLException: Conversion to String failed
    at oracle.sql.Datum.stringValue(Datum.java:181)
    at oracle.jdbc.driver.ArrayDataResultSet.getString(ArrayDataResultSet.java:155)
    at org.wso2.carbon.dataservices.core.engine.ResultSetWrapper.getString(ResultSetWrapper.java:58)
    at org.wso2.carbon.dataservices.core.description.query.SQLQuery.getDataEntryFromRS(SQLQuery.java:1143)
    at org.wso2.carbon.dataservices.core.description.query.SQLQuery.getAllDataEntriesFromRS(SQLQuery.java:960)
    at org.wso2.carbon.dataservices.core.description.query.SQLQuery.processStoredProcQuery(SQLQuery.java:920)
    at org.wso2.carbon.dataservices.core.description.query.SQLQuery.runQuery(SQLQuery.java:2044)
    at org.wso2.carbon.dataservices.core.description.query.Query.execute(Query.java:252)
    at org.wso2.carbon.dataservices.core.engine.CallQuery.execute(CallQuery.java:185)
    at org.wso2.carbon.dataservices.core.engine.CallQueryGroup.execute(CallQueryGroup.java:110)
    at org.wso2.carbon.dataservices.core.description.operation.Operation.execute(Operation.java:71)
    at org.wso2.carbon.dataservices.core.engine.DataService.invoke(DataService.java:592)
    at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:99)
    "

    Could you please let me know as what could be the issue. I did make sure that the xsdType match that of the procedure output.?

    ReplyDelete
    Replies
    1. Looks like the , code was not accepted.You can refer to the "http://stackoverflow.com/questions/15108432/wso2-dataservice-facing-unable-to-retrieve-udt-value-error-when-executin" for the code.

      Delete
  4. Hi Dinusha. It´s possible with WSO2 DSS call a procedure that return an UDT array? Please checkout this question:

    http://stackoverflow.com/questions/32443270/call-a-procedure-or-funcion-in-oracle-db-with-return-array-of-udt-from-wso2-ds

    Thanks,
    Jorge.

    ReplyDelete