How to call a Oracle Stored Function from WSO2 Data Service?

1. Create table executing the following command.

CREATE TABLE TEAMS(id INTEGER, team VARCHAR(30));

2. Following is the stored function which returns total number of entries in the above table.

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR, eid IN NUMBER) RETURN INTEGER 

AS myCount INTEGER;

BEGIN

    INSERT INTO TEAMS values(eid, ename);

    SELECT COUNT(*) into myCount from TEAMS;

    RETURN myCount;

END;

/

3.  Create the data service using following content.

<data name="testOracleFunction">

   <config id="or">

      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>

      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:user/pwd@localhost:1521/XE</property>

      <property name="org.wso2.ws.dataservice.user">user</property>

      <property name="org.wso2.ws.dataservice.password">pwd</property>

   </config>



   <query id="q1" useConfig="or">

      <sql>{call ?:=myfunction(?,?)}</sql>

      <result element="TotalTeams" rowName="">

         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />

      </result>

      <param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />

      <param name="ename" sqlType="STRING" ordinal="2" />

      <param name="eid" sqlType="INTEGER" ordinal="3" />

   </query>



   <operation name="op1">

      <call-query href="q1">

         <with-param name="ename" query-param="ename" />

         <with-param name="eid" query-param="eid" />

      </call-query>

   </operation>

</data>

Note the sql query used to call the stored function: "{call ?:=myfunction(?,?)}". First input parameter carries the return value of function. Other two parameters are inputs to the function.
Important thing is we need to define a Input parameter with OUT type to get the result of function(i.e the first parameter in above sql query). Then we need to define a Output parameter to get this value as a result set from data service. Following section in the data service is used to do this.

<result element="TotalTeams" rowName="">
         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
</result>
<param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />