Oracle使用utl_http訪問webservice
PLSQL訪問webservice, utl_dbws是個不錯的選擇,另外的一種方式是直接構造http請求訪問。
這裡,使用了utl_http工具包,並使用了XMLTABLE+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工具包,並使用了XMLTABLE+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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- WebService 訪問問題Web
- Xcelsius 訪問webservice 需要配置Web
- 十九、.net core使用SoapCore開發webservice介面,以及使用HttpClientFactory動態訪問webservice介面WebHTTPclient
- Servlet訪問WebService出現錯誤ServletWeb
- cxf設定代理訪問webservice介面Web
- oracle使用utl_http包傳送post請求OracleHTTP
- 使用ADO訪問Oracle問題彙總Oracle
- 使用主機命名方法訪問oracleOracle
- Tomcat+ApacheSOAP部署訪問COM物件的WebService (轉)TomcatApache物件Web
- ORACLE使用Generic Connectivity訪問INFORMIXOracleORM
- 使用UTL_HTTP時遭遇ORA-29273HTTP
- WebService效能問題Web
- Oracle 訪問路徑Oracle
- Oracle外網訪問Oracle
- mono 訪問 oracle、mysqlMonoOracleMySql
- Oracle 表訪問方式Oracle
- Oracle 索引訪問方式Oracle索引
- oracle表訪問方式Oracle
- oracle 限定ip訪問Oracle
- java使用axis 呼叫WCF webservice問題請教JavaWeb
- 使用cman(Connection Manager)穿越防火牆訪問oracle防火牆Oracle
- 請都WebService問題,Web
- Oracle EBS DMZ區訪問問題Oracle
- 用Python訪問OraclePythonOracle
- Oracle訪問表的方式Oracle
- Oracle指令碼(Oracle Scripts) – 檢視索引訪問次數及索引訪問型別Oracle指令碼索引型別
- JBoss和WebService的問題Web
- 使用Oracle Net實現限制特定IP訪問資料庫Oracle資料庫
- 從Oracle訪問SQL Server(GATEWAYS)OracleSQLServerGateway
- ORACLE 訪問MYSQL 配置筆記OracleMySql筆記
- Oracle 單表訪問路徑Oracle
- .net之oraclecommand訪問oracleOracle
- Oracle資料庫訪問控制Oracle資料庫
- 通過Oracle Gateways 訪問SybaseOracleGateway
- 限定client端IP訪問oracle!clientOracle
- 限定client段ip訪問oracle!clientOracle
- 使用utl_http獲取某個http頁面內容HTTP
- 使用 session 訪問計數Session