Saturday, August 27, 2011

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

No comments:

Post a Comment