(转)Consuming Web Services in Oracle
来源:互联网 发布:知商金融官网 编辑:程序博客网 时间:2024/05/17 00:19
(转载地址:http://www.oracle-base.com/articles/9i/consuming-web-services-9i.php)
Consuming Web Services in Oracle
Over the last few years web services have increased in popularity to the point where most new application incorporate them to some degree. At the heart of web services is SOAP (Simple Object Access Protocol), a simple XML based protocol to let applications exchange information over HTTP. For more information about SOAP read theSOAP Tutorial.
Oracle9i allows direct access to web services from PL/SQL using the UTL_HTTP
package. In Oracle10g it will be possible to publish PL/SQL as web services directly from the database, rather than via Oracle9iAS as is currently the case. In this article I'll present a simple example of accessing a web service from PL/SQL.
First the soap_api.sql code must be loaded into the database.
The function below uses the SOAP_API
package to access a web services from PL/SQL. The URL of the WDSL file describing the web service is shown here (http://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.
CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER, p_int_2 IN NUMBER) RETURN NUMBERAS l_request soap_api.t_request; l_response soap_api.t_response; l_return VARCHAR2(32767); l_url VARCHAR2(32767); l_namespace VARCHAR2(32767); l_method VARCHAR2(32767); l_soap_action VARCHAR2(32767); l_result_name VARCHAR2(32767);BEGIN l_url := 'http://www.oracle-base.com/webservices/server.php'; l_namespace := 'xmlns="http://www.oracle-base.com/webservices/"'; l_method := 'ws_add'; l_soap_action := 'http://www.oracle-base.com/webservices/server.php/ws_add'; l_result_name := 'return'; l_request := soap_api.new_request(p_method => l_method, p_namespace => l_namespace); soap_api.add_parameter(p_request => l_request, p_name => 'int1', p_type => 'xsd:integer', p_value => p_int_1); soap_api.add_parameter(p_request => l_request, p_name => 'int2', p_type => 'xsd:integer', p_value => p_int_2); l_response := soap_api.invoke(p_request => l_request, p_url => l_url, p_action => l_soap_action); l_return := soap_api.get_return_value(p_response => l_response, p_name => l_result_name, p_namespace => NULL); RETURN l_return;END;/
The output below shows the function in action.
SELECT add_numbers(1, 5) FROM dual;ADD_NUMBERS(1,5)---------------- 6SQL>SELECT add_numbers(10, 15) FROM dual;ADD_NUMBERS(10,15)------------------ 25SQL>
For further information see:
- UTL_HTTP
- UTL_DBWS (10g)
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
soap_api.sql:
CREATE OR REPLACE PACKAGE soap_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/soap_api-- Author : Tim Hall-- Description : SOAP related functions for consuming web services.-- Ammedments :-- When Who What-- =========== ======== =================================================-- 04-OCT-2003 Tim Hall Initial Creation-- 23-FEB-2006 Tim Hall Parameterized the "soap" envelope tags.-- 25-MAY-2012 Tim Hall Added debug switch.-- 29-MAY-2012 Tim Hall Allow parameters to have no type definition.-- Change the default envelope tag to "soap".-- add_complex_parameter: Include parameter XML manually.-- --------------------------------------------------------------------------TYPE t_request IS RECORD ( method VARCHAR2(256), namespace VARCHAR2(256), body VARCHAR2(32767), envelope_tag VARCHAR2(30));TYPE t_response IS RECORD( doc XMLTYPE, envelope_tag VARCHAR2(30));FUNCTION new_request(p_method IN VARCHAR2, p_namespace IN VARCHAR2, p_envelope_tag IN VARCHAR2 DEFAULT 'soap') RETURN t_request;PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request, p_name IN VARCHAR2, p_value IN VARCHAR2, p_type IN VARCHAR2 := NULL);PROCEDURE add_complex_parameter(p_request IN OUT NOCOPY t_request, p_xml IN VARCHAR2);FUNCTION invoke(p_request IN OUT NOCOPY t_request, p_url IN VARCHAR2, p_action IN VARCHAR2) RETURN t_response;FUNCTION get_return_value(p_response IN OUT NOCOPY t_response, p_name IN VARCHAR2, p_namespace IN VARCHAR2) RETURN VARCHAR2;PROCEDURE debug_on;PROCEDURE debug_off;END soap_api;/SHOW ERRORSCREATE OR REPLACE PACKAGE BODY soap_api AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/soap_api-- Author : Tim Hall-- Description : SOAP related functions for consuming web services.-- Ammedments :-- When Who What-- =========== ======== =================================================-- 04-OCT-2003 Tim Hall Initial Creation-- 23-FEB-2006 Tim Hall Parameterized the "soap" envelope tags.-- 25-MAY-2012 Tim Hall Added debug switch.-- 29-MAY-2012 Tim Hall Allow parameters to have no type definition.-- Change the default envelope tag to "soap".-- add_complex_parameter: Include parameter XML manually.-- --------------------------------------------------------------------------g_debug BOOLEAN := FALSE;PROCEDURE show_envelope(p_env IN VARCHAR2, p_heading IN VARCHAR2 DEFAULT NULL);-- ---------------------------------------------------------------------FUNCTION new_request(p_method IN VARCHAR2, p_namespace IN VARCHAR2, p_envelope_tag IN VARCHAR2 DEFAULT 'soap') RETURN t_request AS-- --------------------------------------------------------------------- l_request t_request;BEGIN l_request.method := p_method; l_request.namespace := p_namespace; l_request.envelope_tag := p_envelope_tag; RETURN l_request;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE add_parameter(p_request IN OUT NOCOPY t_request, p_name IN VARCHAR2, p_value IN VARCHAR2, p_type IN VARCHAR2 := NULL) AS-- ---------------------------------------------------------------------BEGIN IF p_type IS NULL THEN p_request.body := p_request.body||'<'||p_name||'>'||p_value||'</'||p_name||'>'; ELSE p_request.body := p_request.body||'<'||p_name||' xsi:type="'||p_type||'">'||p_value||'</'||p_name||'>'; END IF;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE add_complex_parameter(p_request IN OUT NOCOPY t_request, p_xml IN VARCHAR2) AS-- ---------------------------------------------------------------------BEGIN p_request.body := p_request.body||p_xml;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE generate_envelope(p_request IN OUT NOCOPY t_request, p_env IN OUT NOCOPY VARCHAR2) AS-- ---------------------------------------------------------------------BEGIN p_env := '<'||p_request.envelope_tag||':Envelope xmlns:'||p_request.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/" ' || 'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema">' || '<'||p_request.envelope_tag||':Body>' || '<'||p_request.method||' '||p_request.namespace||' '||p_request.envelope_tag||':encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">' || p_request.body || '</'||p_request.method||'>' || '</'||p_request.envelope_tag||':Body>' || '</'||p_request.envelope_tag||':Envelope>';END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE show_envelope(p_env IN VARCHAR2, p_heading IN VARCHAR2 DEFAULT NULL) AS-- --------------------------------------------------------------------- i PLS_INTEGER; l_len PLS_INTEGER;BEGIN IF g_debug THEN IF p_heading IS NOT NULL THEN DBMS_OUTPUT.put_line('*****' || p_heading || '*****'); END IF; i := 1; l_len := LENGTH(p_env); WHILE (i <= l_len) LOOP DBMS_OUTPUT.put_line(SUBSTR(p_env, i, 60)); i := i + 60; END LOOP; END IF;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE check_fault(p_response IN OUT NOCOPY t_response) AS-- --------------------------------------------------------------------- l_fault_node XMLTYPE; l_fault_code VARCHAR2(256); l_fault_string VARCHAR2(32767);BEGIN l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault', 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/'); IF (l_fault_node IS NOT NULL) THEN l_fault_code := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()', 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval(); l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()', 'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval(); RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string); END IF;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------FUNCTION invoke(p_request IN OUT NOCOPY t_request, p_url IN VARCHAR2, p_action IN VARCHAR2) RETURN t_response AS-- --------------------------------------------------------------------- l_envelope VARCHAR2(32767); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response t_response;BEGIN generate_envelope(p_request, l_envelope); show_envelope(l_envelope, 'Request'); l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1'); UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml'); UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope)); UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action); UTL_HTTP.write_text(l_http_request, l_envelope); l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.read_text(l_http_response, l_envelope); UTL_HTTP.end_response(l_http_response); show_envelope(l_envelope, 'Response'); l_response.doc := XMLTYPE.createxml(l_envelope); l_response.envelope_tag := p_request.envelope_tag; l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()', 'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"'); check_fault(l_response); RETURN l_response;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------FUNCTION get_return_value(p_response IN OUT NOCOPY t_response, p_name IN VARCHAR2, p_namespace IN VARCHAR2) RETURN VARCHAR2 AS-- ---------------------------------------------------------------------BEGIN RETURN p_response.doc.extract('//'||p_name||'/child::text()',p_namespace).getstringval();END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE debug_on AS-- ---------------------------------------------------------------------BEGIN g_debug := TRUE;END;-- ----------------------------------------------------------------------- ---------------------------------------------------------------------PROCEDURE debug_off AS-- ---------------------------------------------------------------------BEGIN g_debug := FALSE;END;-- ---------------------------------------------------------------------END soap_api;/SHOW ERRORS
WDSL file:
<?xml version="1.0" encoding="ISO-8859-1"?>-<definitions targetNamespace="http://www.oracle-base.com/webservices/" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://www.oracle-base.com/webservices/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> -<types> -<xsd:schema targetNamespace="http://www.oracle-base.com/webservices/"> <xsd:import namespace="http://schemas.xmlsoap.org/soap/encoding/"/> <xsd:import namespace="http://schemas.xmlsoap.org/wsdl/"/> </xsd:schema> </types> -<message name="ws_addRequest"> <part name="int1" type="xsd:string"/> <part name="int2" type="xsd:string"/></message> -<message name="ws_addResponse"> <part name="return" type="xsd:string"/></message> -<portType name="CalculatorPortType"> -<operation name="ws_add"> <input message="tns:ws_addRequest"/> <output message="tns:ws_addResponse"/> </operation> </portType> -<binding name="CalculatorBinding" type="tns:CalculatorPortType"> <soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="rpc"/> -<operation name="ws_add"> <soap:operation style="rpc" soapAction="http://oracle-base.com/webservices/server.php/ws_add"/> -<input><soap:body namespace="http://www.oracle-base.com/webservices/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" use="encoded"/></input> -<output><soap:body namespace="http://www.oracle-base.com/webservices/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" use="encoded"/></output> </operation> </binding> -<service name="Calculator"> -<port name="CalculatorPort" binding="tns:CalculatorBinding"> <soap:address location="http://oracle-base.com/webservices/server.php"/> </port> </service> </definitions>
- (转)Consuming Web Services in Oracle
- (转)UTL_DBWS - Consuming Web Services in Oracle 10g
- UTL_DBWS - Consuming Web Services in Oracle 10g Onward
- Consuming XML Web Services in iPhone Applications
- Consuming Oracle Service Bus Proxy Services in Oracle JDeveloper with WSIL
- Consuming XML and JSON web services (MyMoviesWithHttpClient)
- 【Mule】Consuming .NET WCF SOAP web services from Mule
- Consuming P6 Web Services over HTTPS (SSL) From Java
- (转)oracle直接调用web services
- Oracle Web Services Manager
- Creating Web Services In .NET
- Tutorial 1: Creating Web Services in Eclipse (Bottom Up)[转]
- oracle直接调用web services
- oracle直接调用web services
- oracle直接调用web services
- Consuming a RESTful Web Service
- Consuming a RESTful Web Service
- Consuming a RESTful Web Service
- 预编译头文件(precompiled header)
- 排序
- gdb 调试入门
- 中国移动 待遇之我见
- cocos2d-x学习笔记(三)让精灵按照自己设定的运动轨迹行动(曲线移动)。(以椭圆轨迹为例)。
- (转)Consuming Web Services in Oracle
- hibernate外键对应设置
- 你猜它是个什么点
- Linux 创建 Oracle 11g r2 asm磁盘出错
- 黑马程序员_Java中的多线程(线程的生命周期)
- iOS中如何判断是否有网络
- android 属性系统
- 还有这样的形状
- 盘点2012年十大神优化与渣优化游戏