“Si tu ne vas pas a Lagardere, Lagardere ira a toi.”[1] | ||
--Le Bossu, Alexandre Dumas |
This chapter takes you through the required steps for turning your database into a Web services consumer and explains how to invoke external Web services from within SQL, PL/SQL, or Java in the database. To begin, as already covered in Chapter 14, why should you consider calling external Web services from the database?
In Chapter 17, I describe how the new corporate registry application of the British Columbia government in Canada exchanged data produced between the Oracle database and its legacy mainframe, which processes part of the application using a set of Web services initiated by database triggers. I must add that Web services call-out is not a response to massive data transfer or replication across systems; the Oracle database furnishes more appropriate mechanisms such as Streams for that function.
In summary, you might consider calling out external Web services from the Oracle database when you need to implement applications such as the following:
Federate legacy/heterogeneous data; build new applications that combine data from multiple sources, including Web services
Automate the tracking and analysis of external and dynamic data (e.g., stock price, scientific data, policy tables, tax tables, weather information)
Invoke business operations implemented as external Web services, such as placing new orders, orders shipment, credit card payment transactions (and getting valid authorization numbers)
In this section, we’ll look at the software pieces and the steps for invoking external Web services from within the database.
As illustrated in Figure 16.1, the following software pieces come into play during the invocation of the external Web services:
The Java Web Service Client stack.
A Web Services proxy Java class generated by JPublisher for each external Web service. Static invocation refers to the generation of a Web service client proxy, which is loaded into the database for calling external Web services. Similar to CORBA Client Stubs (for old-timers), this proxy simplifies Web services programming. The client proxy handles the construction of the SOAP request and the marshaling and unmarshaling of parameters.
A PL/SQL wrapper (Call Spec) to be used by SQL and PL/SQL for invoking operations in the static Web service proxy class. As discussed in Part I, Call Specs allow invoking Java methods from SQL and PL/SQL.
A table function on top of the PL/SQL wrapper. A table function[2]treats the range of returned values from a single or multiple Web services invocations as a table. The rows of such table function are typically pipelined (i.e., streamed directly to the consumer without intermediate storing).
The Java DII stack is an alternative to the static proxy part of the Java Web service client stack that dynamically inspects the Web services WSDL to determine the supported operations and their profiles, and then constructs the SOAP request without generating and using a static client proxy.
The UTL_DBWS is a PL/SQL package, which is a wrapper on top of the Java DII.
XML to SQL mapping: Web service sends data in XML format that can be used and stored as is using XDB; however, you probably and predominantly want to deal with SQL data. Table 16.1 gives a mapping of XML types (originating from Web services) to SQL types (consumed by the database).
The following simple steps let you invoke external Web services from the Oracle database.
Step 1:Load the Web services call-out utility in the database:
You need to configure your database to consume/invoke external Web services. See section 16.3 for more information hereafter.
Step 2: Identify the target Web services and save the location of the WSDL and the endpoint where the Web services are listening to requests; in one of the following examples, the WSDL is located at:
http://services.xmethods.net/soap/urn:xmethods-delayed- quotes.wsdl
In this example, the Analyze WSDL
link takes you to the location of the endpoint, where the Web services are listening to incoming requests; in this example, the endpoint is located at http://64.124.140.30:9090/soap
. The “Operations
” link points to the description of supported operations.
Step 3a: Use static invocation:
Invoke JPublisher with the WSDL location and the endpoint as follows; it will generate the Java WS client proxy (which can be used directly by Java in the database), the PL/SQL wrappers, and a Table Function interface; it will then load everything in the database:
$ 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
The following JPublisher options support Web service call-outs:
proxywsdl
: The URL of the WSDL file for the Web service to be. Note: You might need to enclose the WSDL URL with”” if it contains wild char such as”?”.
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. Note: replace the Oracle HTTP proxy used below with yours.
sysuser
: The database user (and password) with SYSDBA privileges allows Oracle JPublisher to load the generated file into the database. Note: you may need to configure SYS account for remote access. If this option is not declared, you must manually load the generated file into the database to invoke the Web service from PL/SQL.
proxyopts
: A list of options specific to proxywsdl
.
dir
: The directory storing all the generated files.
Following the JPublisher command, you can invoke the Web service operation either directly from Java in the database or from SQL and PL/ SQL through the PL/SQL wrapper. The following SQL script invokes the Quote Web services through the Static proxy wrapper:
SQL> select jpub_plsql_wrapper.getQuote('ORCL') as Quote from dual; QUOTE ---------- 12.6
That’s it, because you don’t have to know the Web services technologies; you just need to be able to locate the WSDL and the endpoint of the service, and then JPub takes care of the rest.
You may also use the table function. The JPublisher proxyopt option specifies the generation of a table function (-proxyopts=tabfun
). Here’s how you would use the table function resulting from the invocation of the getQuote
Web services with a list of parameters (selected from a table of symbols):
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
Issue the following SQL command, which invokes the Web services iteratively on each symbol in the parameter list, and then pipeline the return values as a table (i.e., result set). Ideally, you’d want a Web service that 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.
The output shows the quotes of the given symbols (at the time of the execution of this command). You may apply SQL operators (e.g., MIN, MAX, AVG
) on the table function. You may also store the values and schedule a batch job, which periodically collects these values, and then analyze the table using the power of SQL.
Step 3b:Use dynamic invocation (a bit more complex syntax):
Alternatively to the static proxy option, you may use the dynamic invocation interface (DII). The UTL_DBWS package is described in detail in the “Oracle Database 10g PL/SQL Packages and Type Reference.” Table 16.2 summarizes the UTL_DBWS functions and procedures.
Table 16.2. UTL_DBWS Functions and Procedures
Subprogram | Description |
---|---|
CREATE_CALL Function | Creates a Call instance |
CREATE_SERVICE Function | Creates a Service instance |
GET_IN_PARAMETER_TYPES Function | Lists the XML type of the input parameters of the Call that is returned |
GET_OUT_PARAMETER_TYPES Function | Lists the XML type of the output parameters of the Call that is returned |
GET_OUTPUT_VALUES Function | Obtains the output arguments after a Call invocation |
GET_PORTS Function | Lists the qualified names of all of the ports in a service |
GET_PROPERTY Function | Returns the value of a particular property on a Call |
GET_RETURN_TYPE Function | Lists the XML type that is returned by the given Call |
GET_SERVICES Function | Lists the qualified names of the services defined in a WDSL document |
INVOKE Function | Invokes a specific operation using a synchronous request-response interaction mode |
RELEASE_ALL_SERVICES Procedure | Releases all Service instances |
RELEASE_CALL Procedure | Releases a particular Call instance |
RELEASE_SERVICE Procedure | Releases a particular Service instance |
REMOVE_PROPERTY Procedure | Clears the value of a particular property on a Call |
SET_PROPERTY Procedure | Sets the value of a particular property on a Call |
The dynamic invocation using RPC-style messages works well with XML types that can be mapped into built-in SQL or PL/SQL types but may face serialization issues with complex XML types. DII with document-style messaging does not have these serialization issues.
Here is an example of DII invocation using RPC message style. Note, this is not about the same Web service:
set serveroutput on size 999999 drop table diioutput; create table diioutput (line varchar2(3000)); declare service_ sys.utl_dbws.SERVICE; call_ sys.utl_dbws.CALL; service_qname sys.utl_dbws.QNAME; port_qname sys.utl_dbws.QNAME; operation_qname sys.utl_dbws.QNAME; string_type_qname sys.utl_dbws.QNAME; retx ANYDATA; retx_string VARCHAR2(100); retx_len number; params sys.utl_dbws.ANYDATA_LIST; begin service_qname := sys.utl_dbws.to_qname(null, 'DBRpcEchoService'), service_ := sys.utl_dbws.create_service(service_qname); port_qname := sys.utl_dbws.to_qname(null, 'EchoInterfacePort'), operation_qname := sys.utl_dbws.to_qname('http://echo.demo.oracle/', 'echoString'), call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname); sys.utl_dbws.set_target_endpoint_address(call_, 'http://localhost:8888/echo/DBRpcEchoService'), sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/'), string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string'), sys.utl_dbws.add_parameter(call_, 's', string_type_qname, 'ParameterMode.IN'), sys.utl_dbws.set_return_type(call_, string_type_qname); params(0) := ANYDATA.convertvarchar('hello'), retx := sys.utl_dbws.invoke(call_, params); retx_string := retx.accessvarchar2; insert into diioutput values('echoString repsonse: ' || retx_string); end; /
Here is an example of DII invocation using document style:
set echo on set serveroutput on size 999999 drop table diioutput; create table diioutput (line varchar2(2000)); declare service_ sys.utl_dbws.SERVICE; call_ sys.utl_dbws.CALL; service_qname sys.utl_dbws.QNAME; port_qname sys.utl_dbws.QNAME; xoperation_qname sys.utl_dbws.QNAME; xstring_type_qname sys.utl_dbws.QNAME; response sys.XMLTYPE; request sys.XMLTYPE; begin service_qname := sys.utl_dbws.to_qname('http://echo.demo.oracle/', 'tns'), service_ := sys.utl_dbws.create_service(service_qname); call_ := sys.utl_dbws.create_call(service_); sys.utl_dbws.set_target_endpoint_address(call_, 'http://localhost:8888/echo/DBDocEchoService'), request := sys.XMLTYPE('<tns:echoStringElement xmlns:tns="http://echo.demo.oracle/"> <tns:s>Bob</tns:s></tns:echoStringElement>'), response := sys.utl_dbws.invoke(call_, request); insert into diioutput values('echoString:: response : '), insert into diioutput values(response.getstringval); request := sys.XMLTYPE('<tns:echoBeansElement xmlns:tns="http://echo.demo.oracle/"> <tns:b> <tns:age>13</ tns:age> <tns:name>Bob</tns:name> </tns:b> <tns:b> <tns:age>33</tns:age> <tns:name>Smith</tns:name> </tns:b> </tns:echoBeansElement>'), response := sys.utl_dbws.invoke(call_, request); insert into diioutput values('echoBean[]:: response : '), insert into diioutput values(response.getstringval); end; /
In order to turn an Oracle Database into a Web service consumer, you need to perform the following one-time configuration: ensure that Java is installed in the database; ensure that JPublisher is installed on the client machine; and install the call-out utility.
If you have been running the examples in Part I, you can skip this section. The following basic test will tell your database whether Java is enabled or not:
$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 5 16:54:21 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options SQL> select dbms_java.longname('coffee') from dual; DBMS_JAVA.LONGNAME('COFFEE') ------------------------------- coffee SQL>
Otherwise, install Java from a download or the main Oracle Database CD. In order to get the natively compiled libraries of the system classes and also the ability to natively compile your own classes, you need to additionally install those libraries and the native compiler (NCOMP) from the Oracle Database companion CD.
The following command will tell you whether JPublisher is installed in your client environment:
$ jpub JPub: Java Object Type Publisher, version 10.2.0.0.0 Production Copyright (c) 1997, 2004, Oracle Corporation. All Rights Reserved. JPub generates Java or SQLJ source code for the following SQL entities: object types, collection types, and packages. Invocation: jpub <options> The following option is required: -user=<username>/<password> Other options are: -input=<input file> ...
Otherwise, install SQLJ (JPublisher is currentlly installed as part of SQLJ install) from an Oracle Database download or the client CD through the following steps:
Launch the installer via setup.exe.
Select Custom Install
type.
Select Oracle SQL.
Then proceed with the installation.
The Oracle Database does not currently natively embed the required Web services libraries for call-out, even though the UTL_HTTP
[3] package can be used to construct and unpack SOAP messages programmatically. By loading the Oracle-provided Web services call-out utility in the embedded Java VM, you can instantaneously extend the capabilities of your database.
Step 1: Select the appropriate Web service call-out stack:
This utility is a subset of the Oracle application server Web services stack (made available for free to database customers), you need to load the appropriate version of the libraries that suits your RDBMS release. Select the appropriate call-out utility for your database, using the compatibility matrix shown in Table 16.3:
Table 16.3. Web services Callout Matrix
Target Database Release | Java SE | Oracle AS (OC4J) Release | Call-Out Utility([*]) |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Notes:
It is important that the JDK in your environment be in sync with the J2SE level of the Java runtime in the database; in other words, don’t load code generated with JPublisher under JDK 1.5 in Oracle Database 10g; use JDK 1.4 for 10.2 and 10.1 and JDK 1.3 for 9.2.
Web services call-out supports RPC encoded
in Oracle Database 9.2 and any format in Oracle Database 10g.
Step 2: Extract the contents of the file into your ORACLE_HOME
directory and set the CLASSPATH
environment variable to include the following (replace Windows notation with UNIX equivalent):
%oracle_home%jdbclibojdbc14.jar; // for JDK 1.4 %oracle_home%jdbcliborai18n.jar; %oracle_home%sqljlib ranslator.jar; %oracle_home%sqljlib untime12.jar; %oracle_home%sqljlibdbwsa.jar; %oracle_home%jdbclibclasses12.jar // for JDK 1.3
Step 3: Tune OracleJVM memory:
As explained in Chapter 2, the Java class loader in OracleJVM allocates memory from the Java pool during the resolution of new classes/JARS and for loading their metadata in SGA. You might need to increase the Java pool in order to accommodate the Web service client stack. Make sure that shared_pool_size
and java_pool_size
in the pfile (.ora)
are set at minimum to the following values:
shared_pool_size=96M java_pool_size=80M
You might need to recycle the database if you have altered the pfile
; check with your DBA, if you don’t know.
Step 4: Load the web service client stack:
Load the call-out utility library corresponding to your RDBMS (see Table 16.3).
For 10g RDBMS:
Use the following command if you are loading the JARS in SYS
schema; note "–s –grant public"
for creating synonyms to make the JARS publicly accessibles to other schemas:
$loadjava -u sys/oracle -r -v -f -s -grant public -genmissing sqlj/lib/dbwsclient.jar
Use the following command if you are loading in a user schema such as SCOTT
; note the absence of "–s –grant public"
option; in this case, the libraries will only be accessible to SCOTT schema.
$loadjava -u scott/tiger -r -v -f -genmissing dbwsclient.jar
For 9.2 RDBMS:
You might need to grab some of these jars from an expanded OC4J 10.1.2 (at the ORACLE_HOME
location), if not present in the Call-Out utility. Use the following command to load the Web services client stack into the SYS
schema; for Windows platforms, replace ${ORACLE_HOME}
with %ORACLE_HOME%
:
% loadjava -u sys/change_on_install -r -v -s -f -grant public -genmissing ${ORACLE_HOME}/j2ee/home/lib/jssl- 1_2.jar ${ORACLE_HOME}/soap/lib/soap.jar ${ORACLE_HOME}/dms/lib/dms.jar ${ORACLE_HOME}/j2ee/ home/lib/servlet.jar ${ORACLE_HOME}/j2ee/home/lib/ ejb.jar ${ORACLE_HOME}/j2ee/home/lib/mail.jar
Use the following command to load the Web services client stack into the SCOTT
schema:
% loadjava -u scott/tiger -r -v -f -genmissing ${ORACLE_HOME}/j2ee/home/lib/jssl-1_2.jar ${ORACLE_HOME}/soap/lib/soap.jar ${ORACLE_HOME}/dms/ lib/dms.jar ${ORACLE_HOME}/j2ee/home/lib/servlet.jar ${ORACLE_HOME}/j2ee/home/lib/ejb.jar ${ORACLE_HOME}/ j2ee/home/lib/mail.jar
If you used JDeveloper 10.1.2 to generate the web service client proxy, you also need to load $ORACLE_HOME/jdev/lib/jdevrt.jar
.
At this stage, the RDBMS is ready for calling out external Web services.
Step 5: Enable DII:
The call-out utility loaded in the previous step already installs the Java DII stack; the downloaded ZIP file also contains the SQL scripts utl_dbws_decl.sql
and utl_dbws_body.sql
to create the wrappers on top of the Java DII stack. Execute utl_dbws_decl.sql
and utl_dbws_body.sql
as SYS
. Your RDBMS is ready to use SYS.UTL_DBWS
to call Web services.
Before invoking JPublisher, set the CLASSPATH
environment variable to include the following (replace Windows notation with UNIX equivalent):
%oracle_home%jdbclibojdbc14.jar; //for JDK 1.4 %oracle_home%jdbcliborai18n.jar; %oracle_home%sqljlib ranslator.jar; %oracle_home%sqljlib untime12.jar; %oracle_home%sqljlibdbwsa.jar; %oracle_home%jdbclibclasses12.jar //for JDK 1.3
In order to use Google Web services APIs, you need to register and get a valid license key at http://www.google.com/apis.
Step 1: Locate the Web service:
WSDL: http://api.google.com/GoogleSearch.wsdl
Endpoint: http://api.google.com/search/beta2
Connect to the endpoint from a browser to check that the Web service is up and listening at the endpoint in question; if so, you should receive the following message:
SOAP RPC Router Sorry, I don't speak via HTTP GET- you have to use HTTP POST to talk to me.
Step 2: Invoke JPublisher, as follows:
C:>jpub -u scott/tiger -sysuser=scott/tiger -proxywsdl=http://api.google.com/GoogleSearch.wsdl -proxyopts=tabfun,soap -httpproxy=www-proxy.us.oracle.com:80 -endpoint=http://api.google.com/search/beta2 -dir=tmp tmpGoogleSearchServiceProxyJPub.java tmpplsql_wrapper.sql tmpplsql_dropper.sql tmpplsql_grant.sql tmpplsql_revoke.sql Executing tmpplsql_dropper.sql Executing tmpplsql_wrapper.sql Executing tmpplsql_grant.sql Loading tmpplsql_proxy.jar
When the –plsqlpackage
option is not present, JPublisher uses JPUB_PLSQL_WRAPPER
as the default name of the wrapper.
Granting 'java.lang.RuntimePermission
’ permission to the invoking schema:
This permission is missing in early versions of plsql_grant.sql
: (see Chapter 2 for an explanation of permissions with Java in the database):
SQL> connect / as sysdba Connected. SQL> exec dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission','setFactory', ''), PL/SQL procedure successfully completed.
Step 3: Invoke Web services Spell Check operation through the PL/SQL wrapper:
SQL> select jpub_plsql_wrapper.dospellingsuggestion( 2 '<enter your key here>', 3 'licencing') 4 as GOOGLE_Spell_Check_Web_Service 5 from dual; GOOGLE_SPELL_CHECK_WEB_SERVICE ----------------------------------------------------- licensing SQL> select jpub_plsql_wrapper.dospellingsuggestion( 2 '<enter your key here>', 3 'Nelson Mandelo') 4 as GOOGLE_Spell_Check_Web_Service 5 from dual; GOOGLE_SPELL_CHECK_WEB_SERVICE ------------------------------------------------------ Nelson Mandela
Let’s use the table function interface:
SQL> create table searchtab (key varchar2(32) default '<your key>', word varchar2(30)); Table created. SQL> insert into searchtab (word) values ('Ophrah Winfrid'), 1 row created. SQL> insert into searchtab (word) values ('Mariam Makebo'), 1 row created. SQL> insert into searchtab (word) values ('licenzing'), 1 row created. SQL> commit; SQL> col arg0 format a32 SQL> col arg1 format a30 SQL> col arg1 format a23 SQL> col res format a20 SQL> select * from table(google.to_table_dospellingsuggestion(cursor(select * from searchtab))); ARG0 ARG1 RES -------------------------------- ----------------------- --------------- <your key > Ophrah Winfrid Oprah Winfred <your key > Mariam Makebo Mariam Makeba <your key > licenzing licensing
This phone verification Web service (with a free testing at the time of this writing) lets you retrieve public details about a phone number. The following XML document is the SOAP response message template/profile.
HTTP/1.1 200 OK Content-Type: text/xml; charset=utf-8 Content-Length: length <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <CheckPhoneNumberResponse xmlns="http://ws.cdyne.com/ PhoneVerify/query"> <CheckPhoneNumberResult> <Company>string</Company> <Valid>boolean</Valid> <Use>string</Use> <State>string</State> <Switch>string</Switch> <RC>string</RC> <OCN>string</OCN> <OriginalNumber>string</OriginalNumber> <CleanNumber>string</CleanNumber> </CheckPhoneNumberResult> </CheckPhoneNumberResponse> </soap:Body> </soap:Envelope>
Step 1: Locate the Web service:
Point your browser to:
http://www.cdyne.com/developers/overview.aspx
Locate “Phone Verifier” and click on Get WSDL.
Locate the endpoint (address location):
<wsdl:port name="PhoneVerifyHttpGet"binding="tns:PhoneVerifyHttpGet"> <http:address location="http://ws.cdyne.com/phoneverify/ phoneverify.asmx" />
Step 2: Invoke JPublisher, as follows:
C:My_DataPMBook>jpub -u scott/tiger -sysuser scott/tiger -proxywsdl=http://ws.cdyne.com/phoneverify/ phoneverify.asmx?wsdl -proxyopts=tabfun -httpproxy=www-proxy.us.oracle.com:80 -endpoint=http://ws.cdyne.com/phoneverify/phoneverify.asmx -dir=phone Multiple ports available. To publish a particular port, use -proxyopts=port:PhoneVerifySoap, or -proxyopts=port:PhoneVerifySoap12. Use the default port PhoneVerifySoap. phonesrcgenproxyPhoneVerifySoapClientJPub.java phoneplsql_wrapper.sql phoneplsql_dropper.sql phoneplsql_grant.sql phoneplsql_revoke.sql Executing phoneplsql_dropper.sql Executing phoneplsql_wrapper.sql Executing phoneplsql_grant.sql Loading phoneplsql_proxy.jar
Don’t forget to grant the additional permission:
SQL> connect / as sysdba Connected. SQL> exec dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'setFactory', ''), PL/SQL procedure successfully completed.
Step 3: Invoke the Web services from SQL:
SQL>select jpub_plsql_wrapper.checkphonenumber('6505067000','0') from dual;
This example is also fully highlighted in the Oracle By Example at
http://www.oracle.com/technology/obe/10gr2_db_single/develop/ java/java_otn.htm
These examples are just proof of the ability to invoke external Web services from within the database. Try your own examples, but more important, you need to determine if such an approach makes sense for your specific requirements.
This concludes Part V, which is dedicated to the emerging concept of Database Web services. This is just the beginning of a trend; I anticipate that Database Web services will mature and be an integral part of the larger Web services and service-oriented architecture.
The next part of the book “Putting Everything Together” will describe use cases that involve the pieces we have covered in Part I, II, III, IV, and V.
3.141.28.107