| Developer: J2EE
Virtualize Your Oracle Database with Web Services
Explore the Database Web Services capabilities of Oracle Database 10g, as well as their role in a service-oriented architecture.
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.
$ 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://220.127.116.11: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.6You may also schedule a batch job, which will periodically invoke the Web service in question, and stores the results. 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.
Kuassi Mensah is a group product manager in the Server Technologies division at Oracle.