Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
来源:互联网 发布:音箱测试软件 编辑:程序博客网 时间:2024/05/18 01:43
转自:http://blog.csdn.net/pan_tian/article/details/25420947
从PL/SQL,Oracle提供了两种方式调用外部Webservice:一种是UTL_HTTP,另外一种是UTL_DBWS。
- utl_http – low level http assembler
- utl_dbws – high level web service package
UTL_HTTP
•Available as of 8.0.5
•Send/receive raw HTTP request/responses to external servers
•Advantages:
–Simplistic
–Installed (completely) in the database
–Passed and returns a VARCHAR2 XML payload
–Very easy if you know the XML payload structures
–Doesn't require a WSDL at publisher's site
–Good examples available on the internet
–3rdparty PL/SQL wrappers available (Tim Hall: soap_api)
•Disadvantages:
–Low level with no smarts to support web services
–Cryptic HTTP error messages
Oracle documentation is less than usefulUTL_DBWS
- Available as of 10gR1
- PL/SQL Java wrapper on oracle.jpub.runtime.dbws.DbwsProxy
- Part of JPublisher
- Advantages:
- Disadvantages:
-Not completely installed by default (bah!)-Poor error reporting (in particular HTTP)-Queries external WSDL each request-Oracle documentation is dismal-Use of HttpUriType does not support wallets or proxies-Minor bugs in 10gR2 version with external authentication-Minor issues on calling .Net web services-Uses database JVM
UTL_HTTP现在已经逐步取代UTL_DBWS。
UTL_HTTP Sample
DECLARE L_PARAM_LIST VARCHAR2(512); L_HTTP_REQUEST UTL_HTTP.REQ; L_HTTP_RESPONSE UTL_HTTP.RESP; L_RESPONSE_TEXT VARCHAR2(32767);BEGIN -- service's input parameters L_PARAM_LIST := 'FromCurrency=EUR&ToCurrency=USD'; -- preparing Request... L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('http://www.webservicex.net/currencyconvertor.asmx/ConversionRate', 'POST', 'HTTP/1.1'); -- ...set header's attributes UTL_HTTP.SET_HEADER(L_HTTP_REQUEST, 'Content-Type', 'application/x-www-form-urlencoded'); UTL_HTTP.SET_HEADER(L_HTTP_REQUEST, 'Content-Length', LENGTH(L_PARAM_LIST)); -- ...set input parameters UTL_HTTP.WRITE_TEXT(L_HTTP_REQUEST, L_PARAM_LIST); -- get Response and obtain received value L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(L_HTTP_REQUEST); UTL_HTTP.READ_TEXT(L_HTTP_RESPONSE, L_RESPONSE_TEXT); DBMS_OUTPUT.PUT_LINE(L_RESPONSE_TEXT); -- finalizing UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);END;
UTL_DBWS Sample
DECLARE
v_namespace VARCHAR2(1000) := 'http://www.sagecomputing.com.au/emp';
v_service_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employees');
v_port_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employeesSoapHttpPort');
v_operation_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'getName');
v_service utl_dbws.service;
v_call utl_dbws.call;
v_int_type utl_dbws.qname;
v_string_type utl_dbws.qname;
v_request_params utl_dbws.anydata_list; -- RPC style only
v_response_anydata AnyData; -- RPC style only
v_request_xmltype XmlType; -- Document style only
v_response_xmltype XmlType; -- Document style only
BEGIN
v_service := utl_dbws.create_service(
HttpUriType('http://www.sage.com.au/employees?wsdl'), v_service_qname);
v_call := utl_dbws.create_call(v_service, v_port_qname, v_operation_qname);
utl_dbws.set_property(v_call, 'SOAPACTION_USE', 'TRUE');
utl_dbws.set_property(v_call, 'SOAPACTION_URI', 'getName');
utl_dbws.set_property(v_call, 'ENCODINGSTYLE_URI',
'http://schemas.xmlsoap.org/soap/encoding/');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'document');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
v_int_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'int');
v_string_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
utl_dbws.add_parameter(v_call, 'employeeNumber', v_int_type, 'ParameterMode.IN');
utl_dbws.add_parameter(v_call, 'nameCase', v_string_type, 'ParameterMode.IN');
utl_dbws.set_return_type(v_call, v_string_type);
v_request_params(0) := AnyData.convertNumber(1234);
v_request_params(1) := AnyData.convertVarchar('M');
v_response_anydata := utl_dbws.invoke(v_call, v_request_params);
dbms_output.put_line('Result = ' || v_response_anydata.accessVarchar2);
utl_dbws.release_call(v_call);
utl_dbws.release_service(v_service);
END;
/
Result = Christopher Muir
v_namespace VARCHAR2(1000) := 'http://www.sagecomputing.com.au/emp';
v_service_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employees');
v_port_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'employeesSoapHttpPort');
v_operation_qname utl_dbws.qname := utl_dbws.to_qname(v_namespace,'getName');
v_service utl_dbws.service;
v_call utl_dbws.call;
v_int_type utl_dbws.qname;
v_string_type utl_dbws.qname;
v_request_params utl_dbws.anydata_list; -- RPC style only
v_response_anydata AnyData; -- RPC style only
v_request_xmltype XmlType; -- Document style only
v_response_xmltype XmlType; -- Document style only
BEGIN
v_service := utl_dbws.create_service(
HttpUriType('http://www.sage.com.au/employees?wsdl'), v_service_qname);
v_call := utl_dbws.create_call(v_service, v_port_qname, v_operation_qname);
utl_dbws.set_property(v_call, 'SOAPACTION_USE', 'TRUE');
utl_dbws.set_property(v_call, 'SOAPACTION_URI', 'getName');
utl_dbws.set_property(v_call, 'ENCODINGSTYLE_URI',
'http://schemas.xmlsoap.org/soap/encoding/');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'document');
utl_dbws.set_property(v_call, 'OPERATION_STYLE', 'rpc');
v_int_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'int');
v_string_type := utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
utl_dbws.add_parameter(v_call, 'employeeNumber', v_int_type, 'ParameterMode.IN');
utl_dbws.add_parameter(v_call, 'nameCase', v_string_type, 'ParameterMode.IN');
utl_dbws.set_return_type(v_call, v_string_type);
v_request_params(0) := AnyData.convertNumber(1234);
v_request_params(1) := AnyData.convertVarchar('M');
v_response_anydata := utl_dbws.invoke(v_call, v_request_params);
dbms_output.put_line('Result = ' || v_response_anydata.accessVarchar2);
utl_dbws.release_call(v_call);
utl_dbws.release_service(v_service);
END;
/
Result = Christopher Muir
参考:http://psoug.org/reference/utl_http.html
http://www.sagecomputing.com.au/papers_presentations/S301704_v2.0_OOW_Back_to_basics_web_services.ppt
0 0
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- (转)FTP From PL/SQL
- pl/sql调用webservice说明
- Send Emails from PL/SQL
- Send Emails from PL/SQL .
- UTL_HTTP
- Performing SQL Operations from PL/SQL
- Invoking af:exportCollectionActionListener from Java
- Beginning PL/SQL: From Novice to Professional
- pl/sql developer快速输入select * from
- pl/sql developer快速输入select * from
- pl/sql developer快速输入select * from
- ORA-06502 assigning values from SQL to PL/SQL variables
- PL/SQL 自定义快捷键(比如输入s,直接就显示select * from)
- invoking
- Oracle UTL_HTTP(收集汇总有用资料)
- CodeForces 178C3
- 机器学习之逻辑回归和softmax回归及sklearn和tensorflow代码示例
- C语言宏定义的几种简单用法
- Android中常用的设计原则与设计模式
- Nginx搭建负载环境
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- 随机数组
- 装X式的阅读代码,无需开启Android Studio来运行项目
- 安装Grid执行root.sh出现ohasd failed解决方案
- Android自定义View使用总结
- POJ 1860 - Currency Exchange(SPFA正环)
- easyui基础知识
- C++ 内核对象
- 十二周OJ汉诺塔