Oracle实践之EBSPLSQL解析Webservice

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:<approver 1>can.wu

3、<approver 2>copy no._note: to add 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 t

4、rack each copy that you distribute.document controlchange record3dateauthorversionchange reference07-jan-17can.wu1.0no previous documentreviewersnamepositiondistributioncopy no.namelocation1library masterproject library2project manager34note: the copy numbers referenced above should be written into

5、the copy number space on the cover of each 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

6、 the equivalent of the cover page, for document 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 de

7、liverable11open issues11closed issues11note: 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 insert>index and tables, make sure the table of contents tab is active, and change the number of levels

8、 to a new value.open and closed issues for this deliverable 11error! 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主动调用

9、外围系统的ws。如果我们需要外围系统主动调用ebs的ws，那么我们就需要在ebs中发布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开发

10、及部署，因为utl_dbws需要安装，相对麻烦，本章节使用utl_http调用web service。实例对象：vs159环境，http:/vs159.hand-:8005/webservices/soaprovider/plsql/cux_ws_information_pkg/?wsdll 获取web service请求和响应xml可以通过soapui工具或ebs的ws测试页面获取，本例ws的请求xml如下：<soap:envelope xmlns:soap="/soap/envelope/" xmlns:wsu=&

11、quot;/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"> <soap:header><wsse:security xmlns:wsse="/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="/wss/2004/01/oasis-200401

12、-wss-wssecurity-secext-1.0.xsd" xmlns:env="/soap/envelope/" soap:mustunderstand="1"><wsse:usernametoken xmlns:wsse="/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http:/docs.oasis-open.or

13、g/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:username>hand_ws</wsse:username><wsse:password type="/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext">qwer1234</wsse:password></wsse:usernametok

14、en></wsse:security></soap:header> <soap:body xmlns:ns1=" <ns1:inputparameters> <ns1:p_request_data> <ns1:p_request_data_item> <ns1:employee_num>121</ns1:employee_num> <ns1:start_date/> <ns1:end_date/> </ns1:p_request_data_item> &

15、lt;/ns1:p_request_data> </ns1:inputparameters> </soap:body></soap:envelope>ws的响应xml如下： <env:envelope xmlns:env="/soap/envelope/"> <env:header/> <env:body> <outputparameters xmlns:xsi="/2001/xmlschema-in

16、stance" xmlns=" <x_return_status >s</x_return_status> <x_return_count >7</x_return_count> <x_response_data > <x_response_data_item > <hly_company >23121</hly_company> <employee_num >10</employee_num> <vendor_number >29</v

17、endor_number> <vendor_name >sqy,</vendor_name> <tele_number >lt;/tele_number> <depart_code >01</depart_code> <company_name >021</company_name> </x_response_data_item> <x_response_data_item > <hly_company >23121</hly_com

18、pany> <employee_num >12</employee_num> <vendor_number >45</vendor_number> <vendor_name >龚莹,</vendor_name> <tele_number >lt;/tele_number> <depart_code >01</depart_code> <company_name >022</company_name> </x_respons

19、e_data_item> <x_response_data_item > <hly_company >23121</hly_company> <employee_num >14</employee_num> <vendor_number >2012080021</vendor_number> <vendor_name >0007,</vendor_name> <tele_number >lt;/tele_number> <depart

20、_code >01</depart_code> <company_name >023</company_name> </x_response_data_item> <x_response_data_item > <hly_company >23121</hly_company> <employee_num >25</employee_num> <vendor_number >46</vendor_number> <vendor_name >lib

21、by0307,</vendor_name> <tele_number >lt;/tele_number> <depart_code >01</depart_code> <company_name >024</company_name> </x_response_data_item> <x_response_data_item > <hly_company >23121</hly_company> <employee_num >26&l

22、t;/employee_num> <vendor_number >47</vendor_number> <vendor_name >leona,</vendor_name> <tele_number >lt;/tele_number> <depart_code >01</depart_code> <company_name >025</company_name> </x_response_data_item> <x_response_

23、data_item > <hly_company >23121</hly_company> <employee_num >31</employee_num> <vendor_number >48</vendor_number> <vendor_name >allison,</vendor_name> <tele_number >lt;/tele_number> <depart_code >01</depart_code> <

24、;company_name >026</company_name> </x_response_data_item> <x_response_data_item > <hly_company >23121</hly_company> <employee_num >8</employee_num> <vendor_number >0731001</vendor_number> <vendor_name >li, yan</vendor_name> <tel

25、e_number >lt;/tele_number> <depart_code >01</depart_code> <company_name >027</company_name> </x_response_data_item> </x_response_data> </outputparameters> </env:body></env:envelope>plsql调用webservice代码参考ws的请求和响应xml编写pl/sql代码调用ws，

26、代码如下：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) | '" -节点

27、x_table_manufacturer 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; cu

28、rsor c_return(v_xml xmltype) 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 ,&#

29、39;outputparameters/x_return_count') b; cursor c_header(v_xml xmltype) 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

30、_data_item/vendor_number') vendor_number ,trim(extractvalue(value(a) ,'x_response_data_item/vendor_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

31、,trim(extractvalue(value(a) ,'x_response_data_item/company_name') company_name from table(xmlsequence(extract(v_xml ,'outputparameters/x_response_data/x_response_data_item') a;begin -请求信息,包括header验证信息,本例为usernametoken验证 l_soap_content := '<soap:envelope xmlns:soap="http:/

32、/soap/envelope/" xmlns:wsu="/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"> <soap:header><wsse:security xmlns:wsse="/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns=&quo

33、t;/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="/soap/envelope/" soap:mustunderstand="1"><wsse:usernametoken xmlns:wsse="/wss/2004/01/oasis-200401-wss-wssecurity-secex

34、t-1.0.xsd" xmlns="/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:username>hand_ws</wsse:username><wsse:password type="/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext">

35、;qwer1234</wsse:password></wsse:usernametoken></wsse:security></soap:header> <soap:body xmlns:ns1=" <ns1:inputparameters> <ns1:p_request_data> <ns1:p_request_data_item> <ns1:employee_num>121</ns1:employee_num> <ns1:start_date/> <

36、ns1:end_date/> </ns1:p_request_data_item> </ns1:p_request_data> </ns1:inputparameters> </soap:body></soap:envelope>' 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'

37、,sys.utl_http.http_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_re

38、quest); l_http_resp := utl_http.get_response(l_http_req); utl_http.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_resp

39、ond ,chr(38) | 'lt;' ,'<'); l_xml := replace(l_xml ,chr(38) | 'gt;' ,'>'); dbms_output.put_line('=返回报文 begin='); dbms_output.put_line(l_xml); dbms_output.put_line('=返回报文 end='); -替换返回xml信息 转义<> l_xml := replace(l_xml ,l_xmlns_format); selec

40、t instr(l_xml ,'<outputparameters') into l_count from dual; select substr(l_xml ,l_count) into l_xml1 from dual; select instr(l_xml1 ,'</outputparameters') into l_count from dual; select substr(l_xml1 ,0 ,l_count + 18) into l_xml2 from dual; l_xml2 := replace(l_xml2 ,' xmln

41、s=>' ,'>'); -开始解析 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('=返回头信息='); dbms_output.put_line('返回状态 x_return_status=>' | v_cor.x_r

42、eturn_status | ''); dbms_output.put_line('返回行数 x_return_count=>' | v_cor.x_return_count | ''); dbms_output.put_line('='); end loop; for v_cor in c_header(v_xml) loop dbms_output.put_line('=行' | l_line_number | '='); dbms_output.put_line('hly_com

43、pany' | ':' | v_cor.hly_company); dbms_output.put_line('employee_num' | ':' | v_cor.employee_num); dbms_output.put_line('vendor_number' | ':' | v_cor.vendor_number); dbms_output.put_line('vendor_name' | ':' | v_cor.vendor_name); dbms_output

44、.put_line('tele_number' | ':' | v_cor.tele_number); dbms_output.put_line('depart_code' | ':' | v_cor.depart_code); dbms_output.put_line('company_name' | ':' | v_pany_name); dbms_output.put_line('='); l_line_number := l_line_number + 1; end loop; dbms_outpu