Oracle存储过程请求WebService并解析返回报文

来源：互联网发布：python for循环实例编辑：程序博客网时间：2024/06/15 13:28

WebService请求报文：

<soapenv:Envelope xmlns:soapenv="" http://schemas.xmlsoap.org/soap/envelope/ ""><soap:Header xmlns:soap="" http://schemas.xmlsoap.org/soap/envelope/ ""></soap:Header><soapenv:Body><car:locationReq xmlns:car="" http://www.sxqc.com/osb/Trans/CarTravellingPathLocation ""><car:vin>EX027448</car:vin></car:locationReq></soapenv:Body></soapenv:Envelope>

WebService响应报文：

<?xml version="1.0" encoding="UTF-8" ?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"></soap:Header><soapenv:Body><ns2:locationResp xmlns:ns2="http://www.sxqc.com/osb/Trans/CarTravellingPathLocation"><ns2:L_RET_STATUS>S</ns2:L_RET_STATUS><ns2:L_RET_MESSAGE/><ns2:L_RET_ERROR_CODE/><ns2:data><ns2:carno/><ns2:state>3</ns2:state><ns2:speed>52.20</ns2:speed><ns2:totalMileage>57631.40</ns2:totalMileage><ns2:locationTime>20171222184105</ns2:locationTime><ns2:address>内蒙古自治区巴彦淖尔市五原县和胜乡110国道</ns2:address><ns2:lng>108.337436</ns2:lng><ns2:lat>41.075663</ns2:lat><ns2:dir>299</ns2:dir></ns2:data></ns2:locationResp></soapenv:Body></soapenv:Envelope>

Oracle存储过程：

create or replace procedure TXJ_TO_ZC_TRANS_HIS(p_vin             in varchar2,                                                r_time            out nocopy varchar2,                                                r_speed           out nocopy varchar2,                                                r_mileage         out nocopy varchar2,                                                r_lt              out nocopy varchar2,                                                r_lg              out nocopy varchar2,                                                r_address         out nocopy varchar2,                                                r_state           out nocopy varchar2,                                                r_ret_status      out nocopy varchar2,                                                r_ret_message     out nocopy varchar2,                                                r_ret_error_code  out nocopy varchar2,                                                response_xml      out nocopy varchar2) is  http_req    utl_http.req;  http_resp   utl_http.resp;  request_env clob;  l_replyline varchar2(2000);  v_sql       varchar2(32767) := '';  type t_cursor is ref cursor;  ids_cursor t_cursor;  id_value   varchar2(32) := '';  v_flag     number := 0;  errorException exception;  errorCode number;  errorMsg  varchar2(1000);  l_body_len number;  l_offset number;  l_max_buffer_len number := 255;  l_buffer varchar2(500);begin  request_env := '<soapenv:Envelope  xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">                  <soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">                  </soap:Header>                  <soapenv:Body>                  <car:locationReq xmlns:car="http://www.sxqc.com/osb/Trans/CarTravellingPathLocation">                  <car:vin>'||p_vin||'</car:vin>                  </car:locationReq>                  </soapenv:Body>                  </soapenv:Envelope>';  http_req := utl_http.begin_request('http://172.16.8.166:7004/TransportationSB/CarTravellingPath/Txj/ProxyService/TransTxjCarTravellingPathLocationSvcProxy?wsdl',                                     'POST',                                     utl_http.HTTP_VERSION_1_1);  utl_http.set_authentication(http_req, 'zc', 'welcome1');  utl_http.set_persistent_conn_support(http_req, TRUE);  utl_http.set_header(http_req, 'Content-Type', 'text/xml;charset=utf-8');  utl_http.set_header(http_req, 'SOAPAction', '');  utl_http.set_body_charset(http_req, 'utf-8');  utl_http.set_header(http_req, 'Content-Length',dbms_lob.getlength(request_env));  l_body_len := dbms_lob.getlength(request_env);  l_offset := 1;  l_buffer := NULL;  WHILE l_offset < l_body_len loop    dbms_lob.read(lob_loc => request_env                  ,amount => l_max_buffer_len                  ,offset => l_offset                  ,buffer => l_buffer);    l_offset := l_offset + l_max_buffer_len;    utl_http.write_text(http_req, l_buffer);    dbms_output.put_line(l_buffer);  END LOOP;  http_resp := utl_http.get_response(http_req);  utl_http.read_text(http_resp, l_replyline);  utl_http.end_response(http_resp);    response_xml := l_replyline;  response_xml := replace(response_xml,'soapenv:','');  response_xml := replace(response_xml,'soap:','');  response_xml := replace(response_xml,'ns2:','');    SELECT RET_STATUS,RET_MESSAGE,RET_ERROR_CODE    INTO r_ret_status,r_ret_message,r_ret_error_code    FROM XMLTABLE('$B/Envelope/Body/locationResp' PASSING                   XMLTYPE(response_xml) AS B COLUMNS                   RET_STATUS VARCHAR2(50) PATH '/locationResp/L_RET_STATUS',                   RET_MESSAGE VARCHAR2(50) PATH '/locationResp/L_RET_MESSAGE',                   RET_ERROR_CODE VARCHAR2(50) PATH '/locationResp/L_RET_ERROR_CODE');  IF r_ret_status = 'S' THEN    SELECT TXJ_SPEED,TXJ_TOTAL_MILEAGE,TXJ_LOCATION_TIME,TXJ_ADDRESS,TXJ_LNG,TXJ_LAT,TXJ_STATE      INTO r_speed,r_mileage,r_time,r_address,r_lg,r_lt,r_state      FROM XMLTABLE('$B/Envelope/Body/locationResp/data' PASSING                     XMLTYPE(response_xml) AS B COLUMNS                     TXJ_CARNO VARCHAR2(50) PATH '/data/carno',                     TXJ_SPEED VARCHAR2(50) PATH '/data/speed',                     TXJ_TOTAL_MILEAGE VARCHAR2(50) PATH '/data/totalMileage',                     TXJ_LOCATION_TIME VARCHAR2(50) PATH '/data/locationTime',                     TXJ_ADDRESS VARCHAR2(50) PATH '/data/address',                     TXJ_LNG VARCHAR2(50) PATH '/data/lng',                     TXJ_LAT VARCHAR2(50) PATH '/data/lat',                     TXJ_STATE VARCHAR2(50) PATH '/data/state');  END IF;  response_xml := l_replyline;EXCEPTION  when errorException then    utl_http.end_response(http_resp);    errorCode    := SQLCODE;    errorMsg     := SUBSTR(SQLERRM, 1, 200);    r_ret_status := 'E';    response_xml := 'l_ret_status=' || r_ret_status || ',errorCode=' ||                    errorCode || ',errorMsg=' || errorMsg;  when others then    utl_http.end_response(http_resp);    errorCode    := SQLCODE;    errorMsg     := SUBSTR(SQLERRM, 1, 200);    r_ret_status := 'E';    response_xml := 'l_ret_status=' || r_ret_status || ',errorCode=' ||                    errorCode || ',errorMsg=' || errorMsg;end TXJ_TO_ZC_TRANS_HIS;

测试结果：

注：Oracle调用http需要ACL权限，https://www.cnblogs.com/Snowfun/p/5577178.html

阅读全文

0 0