ITPub博客

首页 > Linux操作系统 > Linux操作系统 > UTL_DBWS包的创建和用法

UTL_DBWS包的创建和用法

原创 Linux操作系统 作者:paulyibinyi 时间:2009-04-08 22:17:15 0 删除 编辑

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:
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包

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-586802/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6558059