Virtualize Your Oracle Database with Web Services
by Kuassi Mensah

Explore the Database Web Services capabilities of Oracle Database 10g, as well as their role in a service-oriented architecture.

Downloads for this article: Oracle Database 10g Database Web Services Call-out Utility

Published November 2005

As I described in a previous article published in Web Services Journal , Web services provide easy access to remote content and application functionality using industry-standard mechanisms, without any dependency on the provider's platform, the location, the service implementation, or the data format. For that reason, Web services are a principal means of achieving resource virtualization, a key requirement for building enterprise grids.

Service-oriented architecture (SOA) based on standards-compliant middleware gets most of the attention in this area, but Database Web Services, in which database operations are invoked through standard Web services mechanisms such as HTTP and SOAP, are quite popular. According to a 2004 Evans Data Corporation survey, more than two out of three respondents claimed they were in the process of developing or deploying Database Web Services.

Oracle Database 10g can function in two Web services modes: as a Web services consumer (external Web services are invoked from within the database) or Web services provider (clients invoke database operations through the internet/intranet, thereby "virtualizing" those operations). Oracle Database 10g supports both modes—in consumer mode generating Java proxy classes and PL/SQL wrappers and loading the appropriate files in the database for a given Web Services Definition Language (WSDL) file, and in provider mode allowing the publication of PL/SQL, Java stored procedures, SQL queries, and SQL DML as Web services within an SOA. (For more details, see the Technical White Paper, "What's New for Java DB, JDBC, and Database Web Services in Oracle Database 10g." )

In this article, I'll offer high-level explanations for both types of Database Web Services, provide step-by-step instructions for running your own Database Web Service demo, and describe the advantages of integrating your Database Web Services within an SOA.

For more information about developing and deploying Database Web Services, see my book Oracle Database Programming Using Java and Web Services (forthcoming from Elsevier Digital Press at the time of this writing); you can download a "sneak preview" chapter PDF here. The book will contain many more extensive examples of Database Web Services.

The Database as Web Services Consumer

The ability to turn the database as a Web services consumer extends the reach of the database (via SQL query or batch/scheduled jobs) to dynamic data (such as stock prices), data produced on demand (such as credit-card authorization), data that changes periodically (isuch as IRS tables), or legacy systems (through a Web services wrapper), and any data that is typically accessible through Web services mechanisms (such as online bioinformatic/genomic databases).

First, let's examine how to Web service-enable your Oracle database.

Web Service Call-out Utility. The Database Web Service Call-out Utility, available as a free download from OTN (in Oracle9i and Oracle 10g versions), turns your existing database into a Web services consumer by loading a pure Java Web services client stack into your database. This is a one-time operation.

1. Unzip the callout utility under $ORACLE_HOME.
2. Load the dbwsclient.jar file into the SYS schema (for sharing) or into a specific schema where the Web service client will be invoked:

   % loadjava -u sys/change -r -v -f -s -grant public -genmissing   sqlj/lib/dbwsclient.jar
   

Static Web Services Call-Out (Compile Time). Given a WSDL file or a services URL, JPublisher—a database utility that generates Java classes to represent database entities in a Java client program—performs three operations:

• Generates the corresponding Java client proxy to be used directly in a Java-enabled and Web services-enabled Oracle database
• Generates a PL/SQL wrapper package on top of the Java proxy so as to allow SQL and PL/SQL to invoke operations on the external Web Services
• Loads (if you choose) everything into the given database schema, provided it has the appropriate permissions

• proxywsdl—the URL of the WSDL file for the Web service to be invoked
• user—the database schema (and password) for which the PL/SQL wrapper is generated
• httpproxy—the HTTP proxy host and port for accessing the WSDL file
• sysuser—a database schema with SYSDBA privileges, which allows JPublisher to load the generated file into the database. If this argument is not declared, you must manually load the generated file into the database.
• proxyopts—a list of options specific to proxywsdl
• dir—the directory storing all the generated files

Let's take a look at an example. The brief demo below uses the getQuote Web service, which is freely available from www.xmethods.net and provides 20-minute delayed stock quotes; you may run this demo using similar dynamic data related Web services.

The WSDL is located at http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl. Click on the "Analyze WSDL" link to get the location of the endpoint, where the Web services is listening to incoming requests. The endpoint is located at http://64.124.140.30:9090/soap; click on the "Operations" link to get the operations and method names.

First, confirm that dbwsa.jar, JDBC, and JPublisher/SQLJ libraries ($OH/sqlj/lib/translator.jar, $OH/sqlj/lib/runtime12.jar) are in the CLASSPATH.

Next, run the following JPublisher command to generate the Web service client proxy and PL/SQL wrapper and transparently load them into the database's JVM. In order for generated code to work properly in the database, make sure the client-side JDK is compatible with the Java runtime. (Oracle9i Release 2 is JDK-1.3.x compatible while Oracle Database 10g is JDK 1.4.x compatible; I used Oracle 10g Release 2 JPublisher in this example.) Also, check with your DBA for the system password.

$ jpub -u scott/tiger -sysuser system/manager 
-proxywsdl=http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl 
-proxyopts=tabfun -httpproxy=www-proxy.us.oracle.com:80 
-endpoint=http://64.124.140.30:9090/soap -dir=quotes
quotes/src/genproxy/NetXmethodsServicesStockquoteStockQuotePortClientJPub.java
quotes/plsql_wrapper.sql
quotes/plsql_dropper.sql
quotes/plsql_grant.sql
quotes/plsql_revoke.sql
Executing quotes/plsql_dropper.sql
Executing quotes/plsql_wrapper.sql
Executing quotes/plsql_grant.sql
Loading quotes/plsql_proxy.jar

SQL> select jpub_plsql_wrapper.getQuote('ORCL') as Quote from dual;
QUOTE
----------
      12.6

Alternatively, you can dynamically generate the proxy, method names, and arguments at runtime using the UTL_DBWS package. This package supports the Dynamic Invocation Interface (DII), a variant of dynamic invocation, which generates the service endpoint interface at runtime. However, dynamic invocation of external Web Services from the database does not currently handle complex types, only XML types that can be mapped into SQL or PL/SQL built-in types.

Web Services Data Sources (Table Function). You can also query and apply SQL functions to the result set from single or multiple Web services invocations via the Oracle Database Table Function mechanism, which allows treating arbitrary result sets as virtual table. (See the Database Web Services Sample Code page for a complete example.)

In the example, above the JPublisher -proxyopts=tabfun option has automatically generated the Table Function wrapper for the Web services in question. For example, in SQL*Plus, here's how you would query the Table Function resulting from invoking the getQuote Web services on a list of symbols (selected from a table of symbols) for which you'd track the quote.

1. First create the table of symbols.

   SQL> create table symbtab (quote varchar2(4));
   SQL> insert into symbtab values('ORCL');
   SQL> insert into symbtab values ('CSCO');
   SQL> insert into symbtab values ('QCOM');
   SQL> insert into symbtab values ('QQQQ');
   SQL> insert into symbtab values ('GOOG');
   SQL> insert into symbtab values ('EBAY');
   SQL> insert into symbtab values ('YHOO');
   SQL> commit
   

2. Second, issue the following SQL command; it invokes the Web services on each symbol in the table then queries the table function (i.e., result set). Ideally you'd want a Web service, which takes several symbols as arguments and returns their values.

   SQL> col ARG0 format a20
   SQL> select * from table(jpub_plsql_wrapper.to_table_getQuote(cursor(select * from symbtab)));
   
   ARG0                        RES
   -------------------- ----------
   ORCL                      12.64
   CSCO                      17.12
   QCOM                       43.4
   QQQQ                    38.2799
   GOOG                     353.58
   EBAY                       38.2
   YHOO                      35.42
   7 rows selected.
   

For more details, see "Options to Facilitate Web Services Call-Out" in the Oracle Database JPublisher Guide or my book mentioned above.

The Database as Web Services Provider

The ability to turn the database into a Web services provider extends the database's functionality to Web services client applications by allowing the execution of database operations and data retrieval through standard Web services mechanisms.

For example, many customers reuse existing database assets—such as PL/SQL stored procedures/functions and packages, Java stored procedures/functions, pre-defined SQL queries, DML, and Oracle Advanced Queuing processes—as standards-based Web services. In other words, the service is implemented using database artifacts and runs within the database, and thus database developers and DBAs do not need Java or Web services programming skills technologies to take advantage of them.

To accomplish that goal you can exploit the ability of JPublisher to generate a Java proxy for an individual or a set of database operation(s). Specifically, you can publish the Java proxy class as a standard J2EE Web services in Oracle Application Server; the latter's integrated Web Services Assembler is extended with modes, arguments, and code generation options to accommodate database models. The resulting Web service endpoint is managed by the Oracle Application Server runtime, like any other J2EE-based Web services deployed on the middle tier.

Upon the invocation of a function on the Web service, the Java proxy invokes the corresponding operation in the database using JDBC. The service is then executed in the database and the result set is wrapped into a SOAP message and sent back to the service requestor.

In addition, JPublisher allows mapping of database types, such as Ref Cursor or PL/SQL Boolean, that are unsupported by the Oracle Application Server Web Services Framework.

For more details, see the JPublisher documentation, the Oracle Database Java Developer's Guide, and the Oracle Application Server Web Services Developer's Guide.

Making Implementation and Packaging Choices

Some customers prefer the separation of the Web Services framework from the database (the current packaging of Oracle Database Web Services) while others prefer an integrated packaging (Database Web Services Provider natively supported by the Oracle Database). There are pros and cons to each implementation.

First, it is technically possible to handle WSDL and SOAP requests and responses over HTTP directly in the Oracle Database. But such an approach, although satisfactory for simple Web services, will not meet the scalability requirements of enterprise Web services and SOA (more on that later).

Second, Web services and SOA framework vendors are actively adding new specifications (QOS, interoperability, and so on) or beefing up existing specifications. In my opinion, unlike the Web services client stack that can simply be uploaded, the Web Services Provider and SOA stack is more complex and better handled in an agile middle-tier environment rather than a more stable RDBMS environment.

Third, most vendors, including Oracle, use the same Web services and SOA framework for both the middle and RDBMS tiers, thereby furnishing a consistent Web Services development or assembly, deployment, and management across the database and application server tiers. The next section explains the benefits of such packaging.

Integrating the Oracle Database in Your SOA

SOA is a concept based on Web services standards and specifications, which makes it possible to construct architectures wherein client applications can simply register, discover, and use the services deployed over an enterprise grid. Now that we have seen how database operations can be exposed as a standard Web Service, let's look at the bigger picture: how to make these services part of your corporate SOA.

Publishing database operations as Web services via a Java proxy enables these proxies to inherit the interoperability and QOS of the Oracle Fusion Middleware SOA framework, including WS-Interoperability, Web Services Reliable Messaging, WS-Security, Web Services Management, and BPEL integration:

• Web Services Interoperability. Remember, interoperability is the key to fulfillment of all Web services promises. The WS-I organization specifies what it means for a Web Services framework to be "interoperable" via the WS-I Basic Profile 1.0 spec. Similar to the J2EE certification program, WS-I Basic Profile 1.0 mandates support for SOAP 1.1, WSDL 1.1, HTTP 1.1, HTTP binding (or HTTPS), and XML Schema (Parts 1 and 2). The Oracle Fusion Middleware SOA framework is not only compliant with WS-I Basic Profile 1.0, but also focuses on interoperability with other vendors/players specifics.
• Web Services Reliable Messaging. Oracle Application Server implements reliable messaging for SOAP-based Web services; it is open, and furnishes reliable SOAP messaging between Web services. The message is sent at least once (guaranteed delivery), sent at most once (guaranteed duplicate elimination), or sent exactly once (guaranteed delivery and duplicate elimination). Oracle Application Server currently implements the OASIS standard WS-Reliability and Oracle is working as part of the OASIS WS-ReliableExchange group to converge WS-Reliability with WS-ReliableMessaging to ensure an interoperable, industrywide adopted reliable messaging standard. Oracle Database Web Services can make use of these services within an SOA.
• WS-Security. The WS-Security specification provides secure SOAP messages through XML digital signature, confidentiality through XML encryption, and credential propagation through Username Token and X509 Token. The Oracle Web Services Manager tool lets you secure Database Web Services via WS-Security using agents and gateways.
• Web Services Management. Oracle Web Services Manager's unique management framework furnishes Web services access control, single sign-on, centralized security policy management, enforcement of regulations such as Sarbanes-Oxley, Gramm-Leach-Bliley, and HIPAA, monitoring of WS-Security, and Web Services Reliable Messaging, logging, and auditing. These features are exposed through a series of Java Management Extensions (JMX) Management beans (Mbeans).
• BPEL Integration. BPEL is the emerging standard for business process definition; think of it as the workflow for Web services. A Database Web Service can be part of a complex Web service workflow process orchestrated by Oracle BPEL Process Manager, which provides rpc/encoded as well as doc/literal messaging.

As you can see, Database Web services have a major role to play in an enterprise-level SOA. But from an Oracle DBA and database developer's perspective, the most interesting aspect of Database Web Services is that they help them reuse database assets while shielding them from Java or SOAP, WSDL, and UDDI programming.

Send us your comments