utl_dbws.create_service oracle,UTL_DBWS包的创建和用法

UTL_DBWS - Consuming Web Services in Oracle 10gIn a previous article I presented a method for 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: (10.1.2)

10g: (10.1.3.0)

10g & 11g latest: (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.jarIn 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 (). 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 := '';

l_namespace := '';

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包