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" />
     

ORA-14551: cannot perform a DML operation inside a query

While I was calling a oracle function which initially insert data into a table, I got an error "ORA-14551: cannot perform a DML operation inside a query". I thought to share this post since it can be help to anyone who having the same error.

 Bellow is the oracle function:

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR) RETURN INTEGER
AS eid INTEGER;

BEGIN

    INSERT INTO TEAM values(5,ename);

    SELECT id into eid from TEAM WHERE TEAM.name=ename;

    RETURN eid;

END;

/ 

And I called the function using the command,
SQL> select myfunction('test') from dual; 
which cause to give the above error.


Found that we cannot use 'select' to call functions which perform DML(insert. delete, update) operations.  It should be called using the 'call' command as bellow.

SQL> var myvar NUMBER;
SQL> call myfunction('test') into :myvar;  

Call completed.

SQL> print myvar;
MYVAR
----------
     5