Oracle实践之EBS PLSQL 解析Webservice(2)

Doc Ref: 错误！未找到引用源。

WS的响应XML如下：

S 7 23121 10 29 SQY, 13227765123 01 021 23121 12 45 龚莹, 13227765123 01 022 23121 14 2012080021 0007, 13227765123 01 023 错误！未找到引用源。

File Ref: Oracle实践之EBS PL/SQL解析Web Service

Company Confidential - For internal use only

（一）调用Integrated SOA Gateway发布的Web Service 3 of 11

Doc Ref: 错误！未找到引用源。

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的请求和响应XML编写PL/SQL代码调用WS，代码如下：

DECLARE l_wsdl VARCHAR2(100) := 'http://vs159.hand-china.com:8005/webservices/SOAProvider/plsql/cux_ws_get_employee_info_pkg/?wsdl'; 错误！未找到引用源。

File Ref: Oracle实践之EBS PL/SQL解析Web Service

Company Confidential - For internal use only

（一）调用Integrated SOA Gateway发布的Web Service 4 of 11

Doc Ref: 错误！未找到引用源。

l_soap_content VARCHAR2(30000); l_soap_request VARCHAR2(30000); l_xmlns VARCHAR2(200) := 'xmlns.oracle.com/apps/cux/soaprovider/plsql/cux_ws_information_pkg/get_employee_inf/\ l_xmlns2 VARCHAR2(200) := 'xmlns=' || chr(13) || '\f/\ --节点X_TABLE_MANUFACTURER l_xml VARCHAR2(20000); l_count NUMBER; l_xmlns_format VARCHAR2(200) := '\f/\ 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) 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 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_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 ,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_错误！未找到引用源。

File Ref: Oracle实践之EBS PL/SQL解析Web Service

Company Confidential - For internal use only

（一）调用Integrated SOA Gateway发布的Web Service 5 of 11

Doc Ref: 错误！未找到引用源。

ITEM'))) a; BEGIN --请求信息,包括Header验证信息,本例为UsernameToken验证 l_soap_content := ' 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.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 （一）调用Integrated SOA Gateway发布的Web Service 6 of 11

Company Confidential - For internal use only

错误！未找到引用源。

File Ref: Oracle实践之EBS PL/SQL解析Web Service

Doc Ref: 错误！未找到引用源。

,l_soap_request); 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_respond ,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); SELECT instr(l_xml ,'' ,'>'); --开始解析 SELECT xmltype(l_xml2) 错误！未找到引用源。

File Ref: Oracle实践之EBS PL/SQL解析Web Service

Company Confidential - For internal use only

（一）调用Integrated SOA Gateway发布的Web Service 7 of 11