Web Service Data Sources (Virtual Table Support)

To access data (returned from single or multiple Web service invocations) through a database table, create a virtual table through a Web service data source. This table function allows you to query a set of returned rows as though it were a table.

The client invokes a Web service and the results are stored in a virtual table in the database. You can pass result sets from function to function, allowing you to set up a sequence of transformation without a table holding intermediate results. To reduce memory usage, you can return the result set rows a few at a time within a function.

By using Web services with the table function, you can manipulate a range of input values (from single or multiple Web services) as a real table. In the following example, the inner SELECT creates rows whose columns are used as arguments for invoking the CALL_WS Web service call-out. The table expression could be used in other SQL queries, for constructing views, and so on.

SELECT <some-columns> 
FROM
  TABLE(WS_TABFUN(CURSOR(SELECT s FROM <some_table>))),
WHERE...

Database as Service Consumer for Web Services

You can extend a relational database's storage, indexing, and searching capabilities to include semistructured and nonstructured data (including Web Services) in addition to enabling federated data. By calling Web Services, the database can track, aggregate, refresh, and query dynamic data produced on-demand, such as stock prices, currency exchange rates, and weather information.

An example of using the database as a service consumer would be to call external Web Services from a predefined database job in order to obtain inventory information from multiple suppliers, then update your local inventory database. Another example is that of a Web Crawler: a database job can be scheduled to collate product and price information from a number of sources.

How to Use

The Web services client code is written in SQL, PL/SQL, or Java to run inside the database, which then invokes the external Web service. Figure 12-2 demonstrates how you can call out to a Web service from a Java client within the database by using one of the following methods:

• SQL and PL/SQL call specs - Invoke a Web service through a user-defined function call (generated through JPublisher) either directly within a SQL statement or view or through a variable.
• Pure Java static proxy class - Use JPublisher to pre-generate a client proxy class. which uses JAX-RPC). This method simplifies the Web service invocation as the location of the service is already known without needing to look up the service in the UDDI registry. The client proxy class does all of the work to construct the SOAP request, including marshalling and unmarshalling parameters.
• Pure Java using DII (dynamic invocation interface) over JAX-RPC - Dynamic invocation provides the ability to construct the SOAP request and access the service without the client proxy.

Which method to use depends on if you want to execute from SQL or PL/SQL or from Java classes.

To call out to any Web service through PL/SQL, use the UTL_DBWS PL/SQL package. This package essentially uses the same APIs as the DII classes. See the PL/SQL Packages and Types Reference for a full description of this package.

You can use a Web Services Data Source to process the results from any Web service request as if it was a real database table.

More on Using Database as Service Provider for Web Services