UTL_DBWS - Consuming Web Services in Oracle 10g
In a previous article I presented a method for Consuming
Web Services using a basic SOAP implementation. This article provides
similar functionality, but this time using the UTL_DBWS
package, which is essentially a PL/SQL wrapper over JPublisher.
First,
download the latest copy of the dbwsclient.jar file:
- Pre 10g: dbws-callout-utility.zip (10.1.2)
- 10g: dbws-callout-utility-10R2.zip (10.1.3.0)
- 10g & 11g latest: dbws-callout-utility-10131.zip (10.1.3.1)
Extract the jar file from the zip file into the $ORACLE_HOME/sqlj/lib
directory.
The jar file can be loaded into the SYS schema for everyone to
access, or into an individual schema that needs access to the web client.
# Load into the SYS schema. export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib # 10gR2 loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar # 11g loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar # Load into an individual schema. export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH cd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib # 10gR2 loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar # 11g loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar
In Oracle 10g the UTL_DBWS package
is loaded by default. In Oracle9i the package must be loaded using the
specification and body provided in the zip file.
The function below uses the
UTL_DBWS package to access a web
services from PL/SQL. The URL of the WDSL file describing the web service is
shown here (http://webservices.imacination.com/distance/Distance.jws?wsdl).
The web service returns the city associated with the specified zipcode.
CREATE OR REPLACE FUNCTION get_city_from_zipcode (p_zipcode IN VARCHAR2)
RETURN VARCHAR2
AS
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;
l_result ANYDATA;
l_wsdl_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_service_qname UTL_DBWS.qname;
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;
l_input_params UTL_DBWS.anydata_list;
BEGIN
l_wsdl_url := ‘http://webservices.imacination.com/distance/Distance.jws?wsdl‘;
l_namespace := ‘http://webservices.imacination.com/distance/Distance.jws‘;
l_service_qname := UTL_DBWS.to_qname(l_namespace, ‘DistanceService‘);
l_port_qname := UTL_DBWS.to_qname(l_namespace, ‘Distance‘);
l_operation_qname := UTL_DBWS.to_qname(l_namespace, ‘getCity‘);
l_service := UTL_DBWS.create_service (
wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
service_name => l_service_qname);
l_call := UTL_DBWS.create_call (
service_handle => l_service,
port_name => l_port_qname,
operation_name => l_operation_qname);
l_input_params(0) := ANYDATA.ConvertVarchar2(p_zipcode);
l_result := UTL_DBWS.invoke (
call_handle => l_call,
input_params => l_input_params);
UTL_DBWS.release_call (call_handle => l_call);
UTL_DBWS.release_service (service_handle => l_service);
RETURN ANYDATA.AccessVarchar2(l_result);
END;
/
The output below shows the function in action.
SQL> SELECT get_city_from_zipcode(‘94065‘) FROM dual; GET_CITY_FROM_ZIPCODE(‘94065‘) -------------------------------------------------------------------------------- Redwood City SQL> SELECT get_city_from_zipcode(‘94066‘) FROM dual; GET_CITY_FROM_ZIPCODE(‘94066‘) -------------------------------------------------------------------------------- San Bruno SQL>需要说明的先要安装oracle jvm 用dbca向导来添加添加完成后,再按上面方法创建UTL_DBWS包
摘录自：http://blog.itpub.net/7199859/viewspace-586802