Select sample WS Callouts straight from your Oracle DB

It’s often nice to have the most recent data when you want to test a web service, so in the best scenario you would want to query existing data with some criteria such as active contracts and/or within a certain range.
This example will show you how to get ready-to-use web service callout requests from SQL Developer (or any other SQL tool) which can be used in a Service Bus Console or SoapUI. We will use the HR example database -which can be found in every Oracle DB- as the source.
This is the request which we’ll use:

select '<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
 <v1:EmployeeId>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||emp.employee_id||'</v1:Identifier>
 </v1:EmployeeId>
 <v1:FirstName>'||emp.first_name||'</v1:FirstName>
 <v1:LastName>'||emp.last_name||'</v1:LastName>
 <v1:Department>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||dep.department_id||'</v1:Identifier>
 </v1:Department>
 </v1:IncreaseSalaryRequest>' WSRequest
from employees emp
, departments dep
where emp.department_id = dep.department_id
-- Let's add some conditions which are needed to finish the process succesfully.
and emp.last_name is not null
and dep.department_name = 'IT' -- return the most valuable employees
-- We only need 5 results.
and rownum <= 5;

This will result in five instantly usable requests from live data so we’ll know for sure that all the criteria that should be met for a functional correct case are indeed met:

<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
  <v1:EmployeeId>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>103</v1:Identifier>
  </v1:EmployeeId>
  <v1:FirstName>Alexander</v1:FirstName>
  <v1:LastName>Hunold</v1:LastName>
  <v1:Department>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>60</v1:Identifier>
  </v1:Department>
</v1:IncreaseSalaryRequest>

This query should work on most databases, but it works on an Oracle DB for sure 🙂
Happy testing!

Oracle SQL developer queries an OracleXE11g db on Ubuntu 11.10

2 Replies to “Select sample WS Callouts straight from your Oracle DB”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.