WSO2 MI Data Service. Calling Oracle procedures/functions that return complex types

47 Views Asked by At

I created a simple Oracle function...

function getUsers(p_status number) return osb.t_user_list_test
   is
   v_users osb.t_user_list_test := osb.t_user_list_test();
 begin
   
   if p_status = 1 then
     
      v_users.extend();
      v_users(v_users.last) := osb.t_user_test(ID  => 1,
                               IIN    => '000000000001',
                               FIO    => 'Barak Obama',
                               Status => '1');
                      
   end if;
   
   return v_users;
 
 end;

It works fine and I can get result by using SELECT OSB.PKG_WSO2.GETUSERS(:P_STATUS) FROM DUAL;

But when I try WSO2 MI Data Service I receive an errors.

My code:

<query id="q1" useConfig="ojdbc">
        <sql>{call ?:=OSB.pkg_wso2.getUsers(?)}</sql>
        <result element="Users" rowName="">
                <element name="id" column="out_user[0]" xsdType="integer" />
                <element name="iin" column="out_user[1]" xsdType="string" />
                <element name="fio" column="out_user[2]" xsdType="string" />
                <element name="status" column="out_user[3]" xsdType="integer" />
        </result>
        <param name="out_user"  sqlType="STRUCT" structType="T_USER_LIST_TEST" type="OUT"/>
        <param name="p_status" sqlType="INTEGER" />
    </query>
    <operation name="op1">
        <call-query href="q1">
            <with-param name="p_status" query-param="p_status" />
        </call-query>
    </operation>

Error:

DS Code: UNKNOWN_ERROR
Nested Exception:-
java.sql.SQLException: failed to construct descriptor: Invalid arguments


    at org.wso2.micro.integrator.dataservices.core.description.query.SQLQuery.processPreStoredProcQuery(SQLQuery.java:967)
    at org.wso2.micro.integrator.dataservices.core.description.query.SQLQuery.runPreQuery(SQLQuery.java:2340)
    at org.wso2.micro.integrator.dataservices.core.description.query.Query.execute(Query.java:301)
    at org.wso2.micro.integrator.dataservices.core.engine.CallQuery.executeElement(CallQuery.java:285)
    at org.wso2.micro.integrator.dataservices.core.engine.OutputElement.execute(OutputElement.java:88)
    at org.wso2.micro.integrator.dataservices.core.description.operation.Operation.execute(Operation.java:56)
    at org.wso2.micro.integrator.dataservices.core.engine.DataService.invoke(DataService.java:613)
    at org.wso2.micro.integrator.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:94)
    ... 13 more
0

There are 0 best solutions below