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