本帖最后由 paulyi 于 2014-2-9 15:15 编辑

UTL_DBWS包的创建和用法

UTL_DBWS - Consuming Web Services in Oracle 10gIn 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 [font=NSimsun]UTL_DBWS package, which is essentially a PL/SQL wrapper over JPublisher.
First, download the latest copy of the dbwsclient.jar file:
[list]

[*]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.

[indent]# Load into the SYS schema.export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATHcd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib
# 10gR2loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar
# 11gloadjava -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:$PATHcd /u01/app/oracle/product/10.2.0/db_1/sqlj/lib
# 10gR2loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar
# 11gloadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar
[/indent]In Oracle 10g the [font=NSimsun]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 [font=NSimsun]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.

[indent]CREATE OR REPLACE FUNCTION get_city_from_zipcode(p_zipcode IN VARCHAR2)
RETURN VARCHAR2AS 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;
/[/indent]
The output below shows the function in action.

[indent]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包
[/indent]