Author: Kuassi Mensah

Web services are a major way to achieve resource virtualization and uses industry standard mechanisms to provide access to remote content and application functionality without depending on the provider's platform, location, service implementation or the data format.

Database Web Services:

Database Web Services invokes database operations through standard Web services mechanisms such as HTTP and SOAP, are quite popular. Oracle Database 10 g supports two Web services modes:

1. A Web services consumer in which external Web services are invoked from within the database.
2. A Web services provider in which clients invoke database operations through the internet/intranet.

Oracle Database Consumer Mode:

• The Oracle Database 10 g consumer modes generate Java proxy classes and PL/SQL wrappers.
• It then loads the appropriate files in the database for a given Web Services Definition Language (WSDL) file.

Oracle Database Provider Mode:

• The Oracle Database 10 g provider mode allow the publication of PL/SQL, Java stored procedures, SQL queries, and SQL DML as Web services within an SOA.

Enabling Consumer Web Services on Oracle Database:

Web services can be enabled on your Oracle database by below means.

The Database Web Service Call-out Utility:

The Database Web Service Call-out Utility 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. The steps include

1. First unzip the callout utility under $ORACLE_HOME.
2. Then load the dbwsclient.jar file into the SYS schema 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

Next, for calling each Web service from the database either static invocation or dynamic invocation is performed. However, static invocation is the recommended one.

JPublisher for Web Service Call-Outs:

JPublisher is a database utility that generates Java classes to represent database entities in a Java client program. Oracle 10 g Release 2 JPublisher supports complex types such as arrays of base types and JavaBeans.

When JPublisher is given a WSDL file or a services URL, it performs three operations:

1. Generates the corresponding Java client proxy to be used directly in a Java-enabled and Web services-enabled Oracle database
2. 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
3. Loads (if you choose) everything into the given database schema, provided it has the appropriate permissions

You will need to specify several JPublisher options for Web service call-outs:

1. Confirm that dbwsa.jar, JDBC, and JPublisher/SQLJ libraries ($OH/sqlj/lib/translator.jar, $OH/sqlj/lib/runtime12.jar) are in the CLASSPATH.
2. 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.
3. Make sure the client-side JDK is compatible with the Java runtime.
4. 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

5. You may also schedule a batch job, which will periodically invoke the Web service in question, and stores the results.

Another option is to dynamically generate the proxy, method names, and arguments at runtime using the UTL_DBWS package. This package supports the Dynamic Invocation Interface (DII) 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:

Oracle Database Table Function mechanism allows treating arbitrary result sets as virtual table. You can query and apply SQL functions to the result set from single or multiple Web services invocations via it.

In the example, above the JPublisher -proxyopts=tabfun option has automatically generated the Table Function wrapper for the Web services in question.

In SQL*Plus you can 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. 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.  Issue the following SQL command which invokes the Web services on each symbol in the table then queries the table function (i.e., result set). 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.

Enabling Provider Web Services on Oracle Database:

To accomplish this goal you can exploit the ability of JPublisher to generate a Java proxy for an individual or a set of database operation(s). Publish the Java proxy class as a standard J2EE Web services in Oracle Application Server. The resulting Web service endpoint is managed by the Oracle Application Server runtime.

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.

Packaging:

Some customers prefer the separation of the Web Services framework from the database while others prefer an integrated packaging. However there are pros and cons to each implementation.

1. Handling WSDL and SOAP requests and responses over HTTP directly in the Oracle Database will not meet the scalability requirements of enterprise Web services and SOA.
2. Web services and SOA framework vendors are actively adding new or beefing up existing specifications. 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.
3. Most vendors, including Oracle, use the same Web services and SOA framework for both the middle and RDBMS tiers. There are benefits of such packaging approach.

Publishing Database Operations as Web services:

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. These include

Web Services Interoperability:

Interoperability is the key to fulfillment of all Web services promises. 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. The message is sent at least once, sent at most once, or sent exactly once.

Web Services Security:

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 tool furnishes Web services access control, single sign-on, centralized security policy management, enforcement of regulations, monitoring of WS-Security, and Web Services Reliable Messaging, logging, and auditing.

BPEL Integration:

BPEL is the emerging standard for business process definition. 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.

Conclusion:

Database Web services play a major role in an enterprise-level SOA. They help in reuse of database assets while shielding them from Java or SOAP, WSDL, and UDDI programming.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Oracle Failure to Spawn!!
   A Guideline to Oracle Server Optimization
   Great Tips for Oracle Cancel-Based Recovery!!
   Performance Impact of Low Cardinality Leading Columns in an Index!!