PLSQL访问webservice, utl_dbws是个不错的选择,另外的一种方式是直接构造http请求访问。
这里,使用了utl_http工具包,并使用了X MLTABLE+XPATH获取请求的结果。
另外,普通数据库用户需要预先授权。
代码如下:
DECLARE
req utl_http.req;
resp utl_http.resp;
v_msg VARCHAR2(80);
v_url VARCHAR2(32767) := 'http://120.131.70.71:8088/svc.asmx';
v_name VARCHAR2(32767);
v_value VARCHAR2(32767);
v_clob_content CLOB;
--这里是请求的内容
v_content VARCHAR2(32767) := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:h2="h2">
<soapenv:Header/>
<soapenv:Body>
<h2:svc_about/>
</soapenv:Body>
</soapenv:Envelope>
';
v_xmltable xmltype;
BEGIN
--utl_http.set_response_error_check ( enable => true );
-- utl_http.set_detailed_excp_support ( enable => true );
--dbms_output.put_line('STATUS CODE: ' || 'resp.status_code');
req := utl_http.begin_request(v_url, 'POST', utl_http.http_version_1_1);
utl_http.set_header(req, 'Content-Type', 'text/xml; charset=utf-8');
--utl_http.set_header(req, 'Host', '220.168.42.13');
utl_http.set_header(req, 'Content-Length', length(v_content));
utl_http.write_text(req, v_content);
resp := utl_http.get_response(r => req);
dbms_output.put_line('STATUS CODE: ' || resp.status_code);
dbms_output.put_line('REASON PHRASE: ' || resp.reason_phrase);
BEGIN
LOOP
utl_http.read_line(resp, v_value, TRUE);
v_clob_content := v_clob_content || v_value;
--dbms_output.put_line(v_value);
END LOOP;
/*FOR i IN 1 .. utl_http.get_header_count(r => resp) LOOP
utl_http.get_header(r => resp,
n => i,
NAME => v_name,
VALUE => v_value);
dbms_output.put_line(v_name || ': ' || v_value);
END LOOP;*/
utl_http.end_response(r => resp);
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line(SQLCODE || ':' || SQLERRM);
--dbms_output.put_line(dbms_utility.format_error_backtrace);
--dbms_output.put_line(dbms_utility.format_error_stack);
utl_http.end_response(r => resp);
END;
--dbms_output.put_line(v_clob_content);
/* v_clob_content := REPLACE(v_clob_content,
' SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"',
'');
v_clob_content := REPLACE(v_clob_content,
' xmlns:ns1="http://www.oracle-base.com/webservices/"',
'');
v_clob_content := REPLACE(v_clob_content, ' xsi:type="xsd:string"', '');
v_clob_content := REPLACE(v_clob_content, 'SOAP-ENV:', '');
v_clob_content := REPLACE(v_clob_content, 'ns1:', '');*/
--dbms_output.put_line(v_clob_content);
v_xmltable := xmltype(v_clob_content);
--这里解析返回的结果 如果有namespace需要在extract参数里声明。
--dbms_output.put_line(v_xmltable.extract('/soap:Envelope/soap:Body/svc_aboutResponse/svc_aboutResult/text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"')
-- .getclobval());
dbms_output.put_line(v_xmltable.extract('/soap:Envelope/soap:Body/svc_aboutResponse/svc_aboutResult/text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="h2"').getclobval());
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ':' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
--utl_http.end_response(r => resp);
END;
这里,使用了utl_http工具包,并使用了X MLTABLE+XPATH获取请求的结果。
另外,普通数据库用户需要预先授权。
代码如下:
req utl_http.req;
resp utl_http.resp;
v_msg VARCHAR2(80);
v_url VARCHAR2(32767) := 'http://120.131.70.71:8088/svc.asmx';
v_name VARCHAR2(32767);
v_value VARCHAR2(32767);
v_clob_content CLOB;
--这里是请求的内容
v_content VARCHAR2(32767) := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:h2="h2">
<soapenv:Header/>
<soapenv:Body>
<h2:svc_about/>
</soapenv:Body>
</soapenv:Envelope>
';
v_xmltable xmltype;
BEGIN
--utl_http.set_response_error_check ( enable => true );
-- utl_http.set_detailed_excp_support ( enable => true );
--dbms_output.put_line('STATUS CODE: ' || 'resp.status_code');
req := utl_http.begin_request(v_url, 'POST', utl_http.http_version_1_1);
utl_http.set_header(req, 'Content-Type', 'text/xml; charset=utf-8');
--utl_http.set_header(req, 'Host', '220.168.42.13');
utl_http.set_header(req, 'Content-Length', length(v_content));
utl_http.write_text(req, v_content);
resp := utl_http.get_response(r => req);
dbms_output.put_line('STATUS CODE: ' || resp.status_code);
dbms_output.put_line('REASON PHRASE: ' || resp.reason_phrase);
BEGIN
LOOP
utl_http.read_line(resp, v_value, TRUE);
v_clob_content := v_clob_content || v_value;
--dbms_output.put_line(v_value);
END LOOP;
/*FOR i IN 1 .. utl_http.get_header_count(r => resp) LOOP
utl_http.get_header(r => resp,
n => i,
NAME => v_name,
VALUE => v_value);
dbms_output.put_line(v_name || ': ' || v_value);
END LOOP;*/
utl_http.end_response(r => resp);
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line(SQLCODE || ':' || SQLERRM);
--dbms_output.put_line(dbms_utility.format_error_backtrace);
--dbms_output.put_line(dbms_utility.format_error_stack);
utl_http.end_response(r => resp);
END;
--dbms_output.put_line(v_clob_content);
/* v_clob_content := REPLACE(v_clob_content,
' SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"',
'');
v_clob_content := REPLACE(v_clob_content,
' xmlns:ns1="http://www.oracle-base.com/webservices/"',
'');
v_clob_content := REPLACE(v_clob_content, ' xsi:type="xsd:string"', '');
v_clob_content := REPLACE(v_clob_content, 'SOAP-ENV:', '');
v_clob_content := REPLACE(v_clob_content, 'ns1:', '');*/
--dbms_output.put_line(v_clob_content);
v_xmltable := xmltype(v_clob_content);
--这里解析返回的结果 如果有namespace需要在extract参数里声明。
--dbms_output.put_line(v_xmltable.extract('/soap:Envelope/soap:Body/svc_aboutResponse/svc_aboutResult/text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"')
-- .getclobval());
dbms_output.put_line(v_xmltable.extract('/soap:Envelope/soap:Body/svc_aboutResponse/svc_aboutResult/text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="h2"').getclobval());
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ':' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
--utl_http.end_response(r => resp);
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-1298762/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-1298762/