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