Oracle利用utl_dbws包 访问WebService

点击(此处)折叠或打开

      今天朋友电话问如何利用Oracle直接调用WebService , 就帮他利用utl_dbws包实现了下，后来发现通过utl_http也可以实现，
并不需要另外安装东西。这次主要说说utl_dbws.
 
utl_dbws包下载地址（包含10G,11G）： https://oracle-base.com/articles/10g/utl_dbws-10g
由于朋友系统是windows 的，就以window为例。

一、utl_dbws包安装：
1、下载解压缩后 目录里有samples，sqlj 两个文件夹，并复制到 ORACLE_HOME目录下。

2、WINDOW  在  ORACLE_HOME/sqlj/lib 目录 执行下列命令
//10G 数据库执行
loadjava -u sys/123456 -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.ja
//11G 数据库执行
loadjava -u sys/123456 -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar
 
3、跑相关脚本与授予权限
10G数据库不需要跑脚本

11G 数据库要跑的脚本并授予权限
sql>@?/sqlj/lib/utl_dbws_decl.sql
sql>@?/sqlj/lib/utl_dbws_body.sql
sql>CREATE PUBLIC SYNONYM utl_dbws FOR sys.utl_dbws;
sql>GRANT EXECUTE ON sys.utl_dbws TO test;   ---给需求用户授予权限

至此安装已经完成了。在库中检查下是否有 utl_dbws包了。


二、调用WebService
 
1、创建Function
 

点击(此处)折叠或打开

1. CREATE OR REPLACE FUNCTION add_wms_hp(str_hpname IN varchar) RETURN number as
   
2.   l_service UTL_DBWS.service;
   
3.   l_call UTL_DBWS.call;
   
4.   l_wsdl_url VARCHAR2(32767);
   
5.   l_namespace VARCHAR2(32767);
   
6.   l_service_qname UTL_DBWS.qname;
   
7.   l_port_qname UTL_DBWS.qname;
   
8.   l_operation_qname UTL_DBWS.qname;
   
9.   l_xmltype_in SYS.XMLTYPE;
   
10.   l_xmltype_out SYS.XMLTYPE;
    
11.   l_return NUMBER;
    
12. BEGIN
    
13.   l_wsdl_url := 'http://192.168.5.112/webservice/SapService.asmx?wsdl'; --配置
    
14.   l_namespace := 'http://tempuri.org/'; --配置
    
15.   l_service_qname := UTL_DBWS.to_qname(l_namespace, 'SapService'); --配置
    
16.   l_port_qname := UTL_DBWS.to_qname(l_namespace, 'SapServiceSoap12');
    
17.   l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'GET9001_114'); --GET9001_114
    
18.   l_service := UTL_DBWS.create_service(wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    
19.                                                service_name => l_service_qname);
    
20.   l_call := UTL_DBWS.create_call(service_handle => l_service,
    
21.                                             port_name => l_port_qname,
    
22.                                             operation_name => l_operation_qname);
    
23.   l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
    
24.     <GET9001_114 xmlns="http://tempuri.org/">
    
25.       <t9001_114s>
    
26.         <T_9001_114>
    
27.           <DESCRIPTION>string</DESCRIPTION>
    
28.           <SOURCE_SYS>string</SOURCE_SYS>
    
29.           <SENDDATE>string</SENDDATE>
    
30.           <ETR_DATE>string</ETR_DATE>
    
31.           <MESG_ID>string</MESG_ID>
    
32.           <MESG_GUID>string</MESG_GUID>
    
33.           <ZSPARE01>string</ZSPARE01>
    
34.           <ZSPARE02>string</ZSPARE02>
    
35.           <ZSPARE03>string</ZSPARE03>
    
36.           <ZSPARE04>string</ZSPARE04>
    
37.           <ZSPARE05>string</ZSPARE05>
    
38.           <ITEMNA>string</ITEMNA>
    
39.           <BRANID>string</BRANID>
    
40.           <MATNR>string</MATNR>
    
41.           <MAKTXCN>string</MAKTXCN>
    
42.           <COLORCODE>string</COLORCODE>
    
43.           <ATTRIBUTE1>string</ATTRIBUTE1>
    
44.           <ATTRIBUTE2>string</ATTRIBUTE2>
    
45.           <ATTRIBUTE3>string</ATTRIBUTE3>
    
46.           <ATTRIBUTE4>string</ATTRIBUTE4>
    
47.           <ATTRIBUTE5>string</ATTRIBUTE5>
    
48.           <ATTRIBUTE6>string</ATTRIBUTE6>
    
49.           <ATTRIBUTE7>string</ATTRIBUTE7>
    
50.           <ATTRIBUTE8>string</ATTRIBUTE8>
    
51.           <ATTRIBUTE9>string</ATTRIBUTE9>
    
52.           <ATTRIBUTE10>string</ATTRIBUTE10>
    
53.           <ATTRIBUTE11>string</ATTRIBUTE11>
    
54.           <ATTRIBUTE12>string</ATTRIBUTE12>
    
55.           <ATTRIBUTE13>string</ATTRIBUTE13>
    
56.           <ATTRIBUTE14>string</ATTRIBUTE14>
    
57.           <ATTRIBUTE15>string</ATTRIBUTE15>
    
58.           <ATTRIBUTE16>string</ATTRIBUTE16>
    
59.           <ATTRIBUTE17>string</ATTRIBUTE17>
    
60.           <ATTRIBUTE18>string</ATTRIBUTE18>
    
61.           <ATTRIBUTE19>string</ATTRIBUTE19>
    
62.           <ATTRIBUTE20>string</ATTRIBUTE20>
    
63.         </T_9001_114>
    
64.         <T_9001_114>
    
65.           <DESCRIPTION>string</DESCRIPTION>
    
66.           <SOURCE_SYS>string</SOURCE_SYS>
    
67.           <SENDDATE>string</SENDDATE>
    
68.           <ETR_DATE>string</ETR_DATE>
    
69.           <MESG_ID>string</MESG_ID>
    
70.           <MESG_GUID>string</MESG_GUID>
    
71.           <ZSPARE01>string</ZSPARE01>
    
72.           <ZSPARE02>string</ZSPARE02>
    
73.           <ZSPARE03>string</ZSPARE03>
    
74.           <ZSPARE04>string</ZSPARE04>
    
75.           <ZSPARE05>string</ZSPARE05>
    
76.           <ITEMNA>string</ITEMNA>
    
77.           <BRANID>string</BRANID>
    
78.           <MATNR>string</MATNR>
    
79.           <MAKTXCN>string</MAKTXCN>
    
80.           <COLORCODE>string</COLORCODE>
    
81.           <ATTRIBUTE1>string</ATTRIBUTE1>
    
82.           <ATTRIBUTE2>string</ATTRIBUTE2>
    
83.           <ATTRIBUTE3>string</ATTRIBUTE3>
    
84.           <ATTRIBUTE4>string</ATTRIBUTE4>
    
85.           <ATTRIBUTE5>string</ATTRIBUTE5>
    
86.           <ATTRIBUTE6>string</ATTRIBUTE6>
    
87.           <ATTRIBUTE7>string</ATTRIBUTE7>
    
88.           <ATTRIBUTE8>string</ATTRIBUTE8>
    
89.           <ATTRIBUTE9>string</ATTRIBUTE9>
    
90.           <ATTRIBUTE10>string</ATTRIBUTE10>
    
91.           <ATTRIBUTE11>string</ATTRIBUTE11>
    
92.           <ATTRIBUTE12>string</ATTRIBUTE12>
    
93.           <ATTRIBUTE13>string</ATTRIBUTE13>
    
94.           <ATTRIBUTE14>string</ATTRIBUTE14>
    
95.           <ATTRIBUTE15>string</ATTRIBUTE15>
    
96.           <ATTRIBUTE16>string</ATTRIBUTE16>
    
97.           <ATTRIBUTE17>string</ATTRIBUTE17>
    
98.           <ATTRIBUTE18>string</ATTRIBUTE18>
    
99.           <ATTRIBUTE19>string</ATTRIBUTE19>
    
100.           <ATTRIBUTE20>string</ATTRIBUTE20>
     
101.         </T_9001_114>
     
102.       </t9001_114s>
     
103.     </GET9001_114>');
     
104. 
     
105.   l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
     
106.                                    request => l_xmltype_in);
     
107.   UTL_DBWS.release_call(call_handle => l_call);
     
108.   UTL_DBWS.release_service(service_handle => l_service);
     
109.   l_return := l_xmltype_out.extract('//return/text()').getNumberVal();
     
110.   RETURN l_return;
     
111. END;
     
112. /

 
 
 
 
 
 
 
 
 

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

转载于:http://blog.itpub.net/31073708/viewspace-2071962/