WSO2 DSS calling Stored Procedure with a UDT

In a case where you have to query a Oracle stored procedure (SP) from WSO2 Data services which contains a  User Defined Types (UDT); you'll have to define the data services definition as follows.

As an example please see the following.

SP (details) with a UDT (m_numbers) defined globally. under database name "clas"

create or replace TYPE  m_numbers AS VARRAY(20) OF VARCHAR2(10);

create or replace PACKAGE  details AS

      PROCEDURE user
      (
            in_name         IN   VARCHAR2,
            in_count        IN   NUMBER,
            out_numbers    OUT  m_numbers,
            out_code       OUT  VARCHAR2,
            out_message    OUT  VARCHAR2
      );
    
    END details;

Then your WSO2 dataservice should be defined as follows.

<data name="testService" transports="http https local">
   <config id="default">
      <property name="driverClassName">oracle.jdbc.driver.OracleDriver</property>
      <property name="url">jdbc:oracle:thin:@//localhost:1521/XE</property>
      <property name="username">xxxx</property>
      <property name="password">xxxx</property>
   </config>
   <query id="getMobileNumberQuery" useConfig="default">
      <sql>call CLAS.DETAILS.USER(:in_name, :in_count, :out_numbers, :out_code, :out_message)</sql>
      <result element="Numbers" rowName="Number">
         <element column="out_code" name="out_code" xsdType="string"/>
         <element column="out_message" name="out_message" xsdType="string"/>
         <element arrayName="m_numbers" column="out_numbers" name="out_numbers" xsdType="string"/>
      </result>
      <param name="in_name" sqlType="STRING"/>
      <param name="in_count" sqlType="INTEGER"/>
      <param name="out_code" sqlType="STRING" type="OUT"/>
      <param name="out_message" sqlType="STRING" type="OUT"/>
      <param name="out_numbers" paramType="ARRAY" sqlType="ARRAY" structType="M_NUMBERS" type="OUT"/>
   </query>
   <operation name="getMobileNumber">
      <call-query href="getMobileNumberQuery">
         <with-param name="in_name" query-param="in_name"/>
         <with-param name="in_count" query-param="in_count"/>
      </call-query>
   </operation>
</data>

If your element definition is wrong you'll be probably end up with the following exception.

[2016-07-24 17:46:03,446] ERROR - SQLQuery DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
java.sql.SQLException: invalid name pattern: clas.details.user

DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
java.sql.SQLException: invalid name pattern: clas.details.user

        at org.wso2.carbon.dataservices.core.description.query.SQLQuery.createProcessedPreparedStatement(SQLQuery.java:1602)
        at org.wso2.carbon.dataservices.core.description.query.SQLQuery.processPreStoredProcQuery(SQLQuery.java:933)
        at org.wso2.carbon.dataservices.core.description.query.SQLQuery.runPreQuery(SQLQuery.java:2303)
        at org.wso2.carbon.dataservices.core.description.query.Query.execute(Query.java:275)
        at org.wso2.carbon.dataservices.core.engine.CallQuery.executeElement(CallQuery.java:188)

[1] http://prabathabey.blogspot.com.au/2012/05/query-udtsuser-defined-types-with-wso2.html