Mapping of SQL Types

Although Oracle Application Server does not currently support LOB types, XMLTYPE, REF CURSORS, and OUT/IN OUT arguments (they will be addressed in future releases), you can use an alternative approach to expose PL/SQL methods and SQL types as Web services.

You can change JPublisher's default behavior to generate code that uses a user-provided subclass. For example, if you have a PL/SQL method that returns a REF CURSOR, JPublisher automatically maps the return type to java.sql.ResultSet. However, this ResultSet type cannot be published as a Web service. To solve this, simply create a new method that can return the result set in a Web service-supported format, such as:

public String [] readRefCursorArray(String arg1, Integer arg2)
  {java.sql.ResultSet rs = getRefCursor(arg1,arg2);
    ... create a String[] from rs and return it... }

Then create an interface that contains the exact methods to publish. You can use JPublisher to easily accomplish this mapping by using the following:

jpub -sql=MYAPP:MyAppBase:MyApp#MyAppInterf...

where:

• MyApp contains the method to return the result set.
• MyAppInterf is the interface that contains the method to publish.

After translating the code for your application, archive all the class files into a single JAR file and use the Web Services Assembler to create a deployable Web service EAR file

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