1、Doc Ref: Error! Reference source not found.Oracle实践EBS PLSQL 实例解析Webservice 实施指南Author:Can.wuCreation Date:06-Jan-2017Last Updated:06-Jan-2017Document Ref:Oracle_Exp_035Version:1.1Note: Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be
2、 easily reproduced in the header and footer of documents. When you change any of these values, be careful not to accidentally delete the bookmark. You can make bookmarks visible by selecting Tools-OptionsView and checking the Bookmarks option in the Show region.Approvals:Can.WuCopy No._Note: To add
3、additional approval lines, press Tab from the last cell in the table above.Note: You can delete any elements of this cover page that you do not need for your document. For example, Copy Number is only required if this is a controlled document and you need to track each copy that you distribute.Docum
4、ent ControlChange Record3DateAuthorVersionChange Reference07-Jan-17Can.Wu1.0No Previous DocumentReviewersNamePositionDistributionCopy No.NameLocation1Library MasterProject Library2Project Manager34Note: The copy numbers referenced above should be written into the Copy Number space on the cover of ea
5、ch distributed copy. If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.Note To Holders:If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for d
6、ocument control purposes.If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.ContentsDocument Controlii介绍1概述1（一）调用Integrated SOA Gateway发布的Web Service2（二）参考文档10Open and Closed Issues for this Deliverable11Open Issues11Closed Issues11N
7、ote: To update the table of contents, put the cursor anywhere in the table and press F9. To change the number of levels displayed, select the menu option InsertIndex and Tables, make sure the Table of Contents tab is active, and change the Number of Levels to a new value.Open and Closed Issues for t
8、his Deliverable 12 of 12Error! Reference source not found.File Ref: Oracle实践之EBS PL/SQL解析Web ServiceCompany Confidential - For internal use only介绍概述在EBS中，我们可以通过PL/SQL或Java调用外围系统发布的WebService（以下简称WS），可以参考文档Oracle实践之DBWS部署及应用&WebService开发及部署中的描述，但这种方式仅是EBS主动调用外围系统的WS。如果我们需要外围系统主动调用EBS的WS，那么我们就需要在EBS中发
9、布WS供其他系统调用。（一）调用Integrated SOA Gateway发布的Web ServiceISG开发的WS，第三方系统调用WS时遇到最多的问题是身份验证相关的问题。本章节主要介绍在项目实施过程中，常见的第三方程序调用EBS的WS示例。1.1 PL/SQL调用Web Service Oracle数据库提供了utl_http和utl_dbws两个程序包，都可以实现Web Service的调用。关于utl_dbws 曾经整理过一篇文档Oracle实践之DBWS部署及应用&WebService开发及部署，因为utl_dbws需要安装，相对麻烦，本章节使用utl_http调用Web Ser
10、vice。实例对象：vs159环境，http:/vs159.hand-:8005/webservices/SOAProvider/plsql/cux_ws_information_pkg/?wsdll 获取Web Service请求和响应XML可以通过soapUI工具或EBS的WS测试页面获取，本例WS的请求XML如下： HAND_WSQWER1234 soap:Body xmlns:ns1= 121 WS的响应XML如下： OutputParameters xmlns:xsi=http:/www.w3.org/2001/XMLSchema-instance xmlns= S 7 23121
11、10 29 SQY, 13227765123 01 021 23121 12 45 龚莹, 13227765123 01 022 23121 14 2012080021 0007, 13227765123 01 023 23121 25 46 libby0307, 13227765123 01 024 23121 26 47 Leona, 13227765123 01 025 23121 31 48 Allison, 13227765123 01 026 23121 8 0731001 li, yan 13227765123 01 027 PLSQL调用WebService代码参考WS的请求和
12、响应XML编写PL/SQL代码调用WS，代码如下：DECLARE l_wsdl VARCHAR2(100) := http:/vs159.hand-:8005/webservices/SOAProvider/plsql/cux_ws_get_employee_info_pkg/?wsdl; l_soap_content VARCHAR2(30000); l_soap_request VARCHAR2(30000); l_xmlns VARCHAR2(200) := l_xmlns2 VARCHAR2(200) := xmlns= | chr(13) | -节点X_TABLE_MANUFACTU
13、RER l_xml VARCHAR2(20000); l_count NUMBER; l_xmlns_format VARCHAR2(200) := l_line_number NUMBER := 1; l_xml1 VARCHAR2(20000); l_xml2 VARCHAR2(20000); v_xml xmltype; l_http_req utl_http.req; l_http_resp utl_http.resp; l_soap_respond VARCHAR2(32767); l_resp_xml xmltype; CURSOR c_return(v_xml xmltype)
14、IS SELECT TRIM(extractvalue(VALUE(a) ,X_RETURN_STATUS) x_return_status ,TRIM(extractvalue(VALUE(b) ,X_RETURN_COUNT) x_return_count FROM TABLE(xmlsequence(extract(v_xml ,OutputParameters/X_RETURN_STATUS) a ,TABLE(xmlsequence(extract(v_xml ,OutputParameters/X_RETURN_COUNT) b; CURSOR c_header(v_xml xml
15、type) IS SELECT TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/HLY_COMPANY) hly_company ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/EMPLOYEE_NUM) employee_num ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/VENDOR_NUMBER) vendor_number ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/VEND
16、OR_NAME) vendor_name ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/TELE_NUMBER) tele_number ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/DEPART_CODE) depart_code ,TRIM(extractvalue(VALUE(a) ,X_RESPONSE_DATA_ITEM/COMPANY_NAME) company_name FROM TABLE(xmlsequence(extract(v_xml ,OutputParamete
17、rs/X_RESPONSE_DATA/X_RESPONSE_DATA_ITEM) a;BEGIN -请求信息,包括Header验证信息,本例为UsernameToken验证 l_soap_content := HAND_WSQWER1234 soap:Body xmlns:ns1= 121 ; l_soap_request := REPLACE(l_soap_content ,l_xmlns); l_soap_request := l_soap_content; l_http_req := utl_http.begin_request(l_wsdl ,POST ,sys.utl_http.ht
18、tp_version_1_1); utl_http.set_header(l_http_req ,Content-Type ,text/xml); utl_http.set_header(l_http_req ,Content-Length ,length(l_soap_request); utl_http.set_header(l_http_req ,SOAPAction ,); utl_http.write_text(l_http_req ,l_soap_request); l_http_resp := utl_http.get_response(l_http_req); utl_http
19、read_text(l_http_resp ,l_soap_respond); utl_http.end_response(l_http_resp); -获取WS响应的XML信息 l_resp_xml := xmltype.createxml(l_soap_respond); - L_DATA:=l_soap_respond l_xml := REPLACE(l_xml ,l_xmlns_format); l_xml := REPLACE(l_soap_respond ,chr(38) | lt; ,); dbms_output.put_line(=返回报文 begin=); dbms_ou
20、tput.put_line(l_xml); dbms_output.put_line(=返回报文 end=); -替换返回XML信息 转义 l_xml := REPLACE(l_xml ,l_xmlns_format); SELECT instr(l_xml ,OutputParameters) INTO l_count FROM dual; SELECT substr(l_xml ,l_count) INTO l_xml1 FROM dual; SELECT instr(l_xml1 , ,); -开始解析 SELECT xmltype(l_xml2) INTO v_xml FROM dual; -Webservice 获取信息 dbms_output.put_line(=解析Webservice 开始=); -获取返回状态 FOR v_cor IN c_return(v_xml) LOOP dbms_output.put_line(=
