来吧,直接上代码。
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/" xmlns:sx="http://www.sx.hrss.gov.cn">
<soapenv:Header/>
<soapenv:Body>
<sx:UploadSwService>
<!--Optional:-->
<sx:ome>
<in:business xmlns:in="http://www.sx.hrss.gov.cn/">
<para>
<SJBLX>'||p_vin||'</SJBLX>
</para>
</in:business>
</sx:ome>
</sx:UploadSwService>
</soapenv:Body>
</soapenv:Envelope>';
http_req := utl_http.begin_request('http://localhost:xx/xxxService/services/xxx?wsdl',
'POST',
utl_http.HTTP_VERSION_1_1);
utl_http.set_authentication(http_req, 'xx', 'xxxxx');
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;