Getting started with Data Services using Oracle

Step 1: Creating a simple Data Service using Scott schema
Following configuration file creates a service based on Oracle's Scott schema.
It creates an operation named 'getAllEmployees'. Using which one can access all
the records in 'emp' table.

Here is the structure of 'emp' table.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Following is all you need to service enable 'emp' table.

<data name="OracleSample1">
   <config>
      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:@wso2-test:1521:orcl</property>
      <property name="org.wso2.ws.dataservice.user">scott</property>
      <property name="org.wso2.ws.dataservice.password">password</property>
   </config>

   <query id="employees">
      <sql>select empno,ename,job,sal from emp</sql>
      <result element="Employees" rowName="Employee">
         <element name="EmpNo" column="empno" />
         <element name="Name" column="ename" />      
         <element name="Job" column="job" />      
         <element name="Salary" column="sal" />      
      </result>
   </query>

   <operation name="getAllEmployees">
      <call-query href="employees" />
   </operation>
</data>

Step 2 : How to pass values using input parameters?

Following example configuration creates an operation named 'getEmployeeWithDepartment'. It requires employee number (empno - NUMBER) to be provided as a parameter.

<data name="OracleSample2">
   <config>
      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:@wso2-test:1521:orcl</property>
      <property name="org.wso2.ws.dataservice.user">scott</property>
      <property name="org.wso2.ws.dataservice.password">password</property>
   </config>

   <query id="employeeWithDept">
      <sql>select emp.empno,emp.ename,emp.job,emp.sal,dept.dname from emp,dept 
      where 
      emp.deptno = dept.deptno 
      and emp.empno = ?
      </sql>
      <result element="Employees" rowName="Employee">
         <element name="EmpNo" column="empno" />
         <element name="Name" column="ename" />      
         <element name="Job" column="job" />      
         <element name="Salary" column="sal" />
         <element name="Department" column="dname" />
      </result>
      <param name="empno" sqlType="INTEGER"/>
   </query>

   <operation name="getEmployeeWithDepartment">
      <call-query href="employeeWithDept">
        <with-param name="empno" query-param="empno" />
      </call-query>
   </operation>
</data>