Oracle® Database Java Developer's Guide 10g Release 1 (10.1) Part Number B12021-02 |
|
|
View PDF |
This chapter provides and overview of database Web services and discusses how to call out to an existing Web service. For information on how to create a Web service, see the Oracle9iAS Web Services Developer's Guide and http://otn.oracle.com/webservices
. For more information on database Web services, see http://otn.oracle.com/tech/webservices/database.html
.
This chapter covers the following topics:
Web services technology enables application-to-application interaction over the Web – regardless of platform, language, or data formats. The key ingredients, including XML, SOAP, WSDL, and UDDI, have been adopted across the entire software industry. Web services technology usually refers to services implemented and deployed in middle-tier application servers. However, in heterogeneous and disconnected environments, there is an increasing need to access stored procedures as well as data and metadata, through Web services interfaces. Database Web services technology is a database approach to Web services.
It works in two directions:
accessing database resources as a Web service
consuming external Web services from the database itself
Turning the Oracle database into a Web service provider leverages investment in Java stored procedures, PL/SQL packages, pre-defined SQL queries and DML. Conversely, consuming external Web services from the database itself, together with integration with the SQL engine, enables Enterprise Information Integration.
This chapter focuses on the advantages of opening up the Oracle Database, through PL/SQL packages and Java classes deployed within the database, to the world of Web services, using the Oracle Application Server and the Oracle database.
Refer to the Oracle9iAS Web Services Developer's Guide for information on:
Testing and securing Web services.
Using PL/SQL-specific legacy types and REF CURSORS.
Writing static or dynamic Java clients to call Web Services.
This chapter also provides a general road map for database Web services, mapping out Web services support for additional database capabilities, such as SQL queries, DML statements, and Java Stored Procedures—through synchronous invocation.
See the Oracle9iAS Web Services Developer's Guide on how to create Web services and invoke them from clients. This chapter covers how to call out to Web services from within the database and how a Web service can call in to the database.
Web Services uses industry-standard mechanisms to provide easy access to remote content and applications, regardless of the provider's platform, location, implementation, or data format. Client applications can query and retrieve data from Oracle databases and invoke stored procedures using standard web service protocols. There is no dependency on Oracle-specific database connectivity protocols. This approach is highly beneficial in heterogeneous, distributed, and non-connected environments.
You can call into the database from a Web Service, using the database as a service provider. This allows you to leverage existing or new SQL, PL/SQL, Java stored procedures, or Java classes within an Oracle database. You can access and manipulate database tables from a Web service client.
Use JPublisher to generate Java wrappers that correspond to database operations, then deploy the wrappers as Web services in Oracle AS. Figure 12-1 demonstrates how you use JPublisher to publish PL/SQL packages, SQL objects, collections, and packages as Java classes. Once published, these objects can be accessed by any Web service through an OC4J Web services servlet.
How to Use
For directions on how to use JPublisher to publish your PL/SQL or SQL objects, collections or packages as Java classes, see the "What JPublisher Can Publish" section in the "Introduction to JPublisher" chapter of the Oracle Database JPublisher User's Guide.
See the Oracle9iAS Web Services Developer's Guide for more information on creating and using Web services.
Features of the Database as a Web Service Provider
Using the database as a Web Service provider offers the following features:
Enhanced PL/SQL Web Services – Improves PL/SQL Web Services by extending Web Services support for additional PL/SQL types including CLOB, BLOB, XMLType, REfCursor, PL/SQL records and tables. This enables you to use most of your existing PL/SQL packages as Web Services.
Java-in-the-database Web Services – Exposes existing Java classes deployed in the database as Web Services. Java classes implementing data-related services can be migrated between the middle tier and the database. Java portability results in database independence.
SQL Query Web Services – Leverages warehousing or business intelligence queries, data monitoring queries, and any predefined SQL statements as web services.
DML Web Services – Offers secure, persistent, transactional and scalable logging, auditing and tracking operations implemented via SQL DML, as web services. DML web services are implemented as atomic or group/batch insert, update, and delete operations.
The following JPublisher features support Web services call-ins to code running in Oracle Database. Refer to the JPublisher Oracle Database JPublisher User's Guide for complete information.
Generation of Java interfaces
JPublisher styles and style files
REF CURSOR returning and result set mapping
Options to filter what JPublisher publishes
Support for calling Java classes in the database without PL/SQL call specs
Support for publishing SQL queries or DML statements
Support for unique method names
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.
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...
Features of the Database as a Web Service Consumer
Using the database as a Web Service consumer offers the following features:
Consuming Web Services form Java-in-the-database – Provides an easy-to-use interface for calling-out web services, thereby insulating developers from low-level SOAP programming. Java classes running in the database can simply and directly invoke external web services by using the previously loaded Java proxy or dynamic invocation.
Consuming Web Services from SQL and PL/SQL – Allows any SQL-enabled tool or application to transparently and easily consume dynamic data from external web services. After Exposing Web Services methods as Java Stored PRocedures, A PL/SQL wrapper on top of a Java stored procedures hides all Java and SOAP programming details from the SQL client.
Web Services Data Source – Enables Application and Data integration by turning external web service into an SQL data source, making external Web services appear as regular SQL tables. This table function represents the output of calling external web services and can be used in an SQL query.
Before generating any stubs or call specs, you must install the UTL_DBWS
package in the database. This package includes both the Java and PL/SQL implementation necessary for facilitating the Web services functionality within the database.
Use the following script to install the Web Services Client Library:
$ORACLE_HOME/sqlj/lib/inctldbws.sql
To remove the Web Services Client Library at a later time, use the following script:
$ORACLE_HOME/sqlj/lib/rmctldbws.sql
JPublisher can receive the WSDL from the Web Service and create the static java, proxy class, or PL/SQL call spec, as shown in Figure 12-4.
This support is created through the following JPublisher key options:
-proxywsdl=
url
Use this option to generate JAX-RPC static client proxies, given the WSDL document at the specified URL. This option also generates additional wrapper classes to expose instance methods as static methods, and generates PL/SQL wrappers. In all, it executes the following steps:
Generates JAX-RPC client proxy classes.
Generates wrapper classes to publish instance methods as static methods.
Generates PL/SQL wrappers (call specs) for classes that must be accessible from PL/SQL.
Loads generated code into the database.
Note: The-proxywsdl option uses the -proxyclasses option behind the scenes for steps 2 and 3, and takes the-proxyopts setting as input. |
Once generated, your database client can access the Web service through PL/SQL using the call specs or through the JAX-RPC client proxy classes. The PL/SQL wrappers use the static methods: your client would not normally access any Web service through the static method directly.
-httpproxy=
proxy_url
Where WSDL is accessed through a firewall, use this option to specify a proxy URL to use in resolving the URL of the WSDL document.
-proxyclasses=
class_list
For Web services, this option is used behind the scenes by the -proxywsdl option and is set automatically, as appropriate. In addition, you can use this option directly, for general purposes, any time you want to create PL/SQL wrappers for Java classes with static methods, and optionally to produce wrapper classes to expose instance methods as static methods.
The -proxyclasses
option takes the -proxyopts
setting as input.
-proxyopts=
wrapper_specifications
This option specifies JPublisher behavior in generating wrapper classes and PL/SQL wrappers—usually, but not necessarily, for Web services. For typical usage of the -proxywsdl
option, the -proxyopts
default setting is sufficient. In situations where you use the -proxyclasses
option directly, you might want to use special -proxyopts
settings.
See the "Additional Features" chapter in the Oracle Database JPublisher User's Guide for more information on how to use JPublisher.
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. Refer to Oracle Database JPublisher User's Guide for more information.
Oracle Database 10g introduces the native Java interface's new features for calls to server-side Java code. This is a simplified application integration: client-side and middle-tier Java applications can directly invoke Java in the database without defining a PL/SQL wrapper. The native Java interface uses server-side Java class reflection.
In previous releases, calling Java stored procedures and functions from a database client required JDBC calls to associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:
The signatures permitted only Java types that had direct SQL equivalents.
Exceptions issued in Java were not properly returned.
The JPublisher -java
option with the Java class or package remedies the deficiencies of JDBC calls to associated PL/SQL wrappers by making convenient use of an API for direct invocation of static Java methods. This functionality is also useful for Web Services.
The functionality of the -java
option is similar to that of the -sql
option, creating a client-side Java stub class to access a server-side Java class, as opposed to creating a client-side Java class to access a server-side SQL object or PL/SQL package. The client-side stub class uses JPublisher code that mirrors the server-side class and includes the following features:
Methods that correspond to the public static methods of the server class
Two constructors: one that takes a JDBC connection, and one that takes the JPublisher default connection context instance
At runtime, the stub class is instantiated with a JDBC connection. Calls to its methods result in calls to the corresponding methods of the server-side class. Any Java types used in these published methods must be primitive or serializable. For example, assume you want to call the following method in the server:
public String oracle.sqlj.checker.JdbcVersion.to_string();
Use the following -java
setting for the JdbcVersion
Java class:
-java=oracle.sqlj.checker.JdbcVersion
When you use the -java
option, you specify a single or multiple server-side Java class or package. If you want to use multiple classes, provide them as a comma-delimited list.
When you use the -java
option, JPublisher generates code similar to the following call:
Connection conn= ... String serverSqljVersion = (String) Client.invoke (conn, "oracle.sqlj.checker.JdbcVersion", "to_string", new Class[] {} );
The Class[]
array is used for the method parameter types while the Object[]
array is used for parameter values. In this case, because to_string
has no parameters, both arrays are empty. This example demonstrates how a Java client outside of the database can call Java methods loaded in the database server. For more information, see Chapter 5, "Command Line Options and Import Files" in Oracle Database JPublisher User's Guide.
Create client stubs using the -java
option of JPublisher.
jpub -u scott/tiger -java=oracle.sqlj.checker.JdbcVersion:CallinImpl#Callin
JPublisher generates a Java interface, Callin
, and its implementation, CallinImpl
. The CallinImpl
class contains a method for each method in oracle.sqlj.checker.JdbcVersion
.
Client invokes methods in the published Java class.
Connection conn=DriverManager.getConnection("jdbc:oracle:oci8", "scott", "tiger"); Callin ci = new CallinImpl(conn); System.out.println("JDBC version inside the server is " + ci.getDriverMajorVersion());
The client code provides the following type of feedback: "JDBC version inside the server is 10.0 (10.0.0.0.0)".