Hi Tim,
i hope that you will have time...
Meanwhile i'm testing another solution, this:
http://www.ora600.be/node/5975The package are:
- Code: Select all
create or replace package flex_ws_util as
/*
Purpose: The package is a companion to the flex_ws_api package
*/
-- get string value
function get_value (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return varchar2;
-- get clob value
function get_value_clob (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return clob;
-- get date value
function get_value_date (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in date := null,
p_date_format in varchar2 := null) return date;
-- get number value
function get_value_number (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in number := null) return number;
/*
-- log web service request
procedure log_request (p_url in varchar2,
p_method in varchar2,
p_request in clob,
p_response in xmltype,
p_request_start_date in date := null,
p_log_text in varchar2 := null,
p_val1 in varchar2 := null,
p_val2 in varchar2 := null,
p_val3 in varchar2 := null);
*/
end flex_ws_util;
/
- Code: Select all
create or replace package body flex_ws_util as
/*
Purpose: The package is a companion to the flex_ws_api package
*/
function get_value (p_xml in xmltype,p_name in varchar2,p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null)
return varchar2 as l_returnvalue varchar2(32767);
begin
/*
Purpose: Get string value from web service response
*/
begin
l_returnvalue := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;
return l_returnvalue;
end get_value;
function get_value_clob (p_xml in xmltype,p_name in varchar2,p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null)
return clob as l_returnvalue clob;
begin
/*
Purpose: Get clob value from web service response
*/
begin
l_returnvalue := flex_ws_api.parse_xml_clob (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;
return l_returnvalue;
end get_value_clob;
function get_value_date (p_xml in xmltype,p_name in varchar2,p_namespace in varchar2 := null,
p_value_if_error in date := null,p_date_format in varchar2 := null)
return date as l_str varchar2(32767);
l_returnvalue date;
begin
/*
Purpose: Get date value from web service response
*/
begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_date (l_str, nvl(p_date_format, 'DD.MM.RRRR HH24:MI:SS'));
exception
when others then
l_returnvalue := p_value_if_error;
end;
return l_returnvalue;
end get_value_date;
function get_value_number (p_xml in xmltype,p_name in varchar2,p_namespace in varchar2 := null,
p_value_if_error in number := null)
return number as l_str varchar2(32767);
l_returnvalue number;
begin
/*
Purpose: Get number value from web service response
*/
begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_number (l_str);
exception
when others then
l_returnvalue := p_value_if_error;
end;
return l_returnvalue;
end get_value_number;
/*
procedure log_request (p_url in varchar2,p_method in varchar2,p_request in clob,
p_response in xmltype,p_request_start_date in date := null,p_log_text in varchar2 := null,
p_val1 in varchar2 := null,p_val2 in varchar2 := null,p_val3 in varchar2 := null)
as pragma autonomous_transaction; l_sysdate date := sysdate;
begin
--Purpose: Log web service request
insert into ws_log (request_start_date, request_end_date,
log_text, ws_url, ws_method,
ws_request, ws_response,
val1, val2, val3)
values (nvl(p_request_start_date, l_sysdate), l_sysdate,
substr(p_log_text,1,4000), substr(p_url,1,4000), substr(p_method,1,4000),
p_request, p_response.getclobval(),
substr(p_val1,1,4000),substr(p_val2,1,4000), substr(p_val3,1,4000));
commit;
*/
end log_request;
end flex_ws_util;
/
- Code: Select all
create or replace TYPE t_soap_envelope AS OBJECT (
/*
Purpose: Object type to handle SOAP envelopes for web service calls
*/
-- public properties
service_namespace varchar2(255),
service_method varchar2(4000),
service_host varchar2(4000),
service_path varchar2(4000),
service_url varchar2(4000),
soap_action varchar2(4000),
soap_namespace varchar2(255),
envelope clob,
-- private properties
m_parameters clob,
constructor function t_soap_envelope (p_service_host in varchar2,p_service_path in varchar2,
p_service_method in varchar2,p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,p_soap_action in varchar2 := null)
return self as result,
member procedure add_param (p_name in varchar2,p_value in varchar2,p_type in varchar2 := null),
member procedure add_xml (p_xml in clob),
member procedure build_env,
member procedure debug_envelope
);
/
- Code: Select all
create or replace type body t_soap_envelope as
/*
Purpose: Object type to handle SOAP envelopes for web service calls
*/
constructor function t_soap_envelope (p_service_host in varchar2,p_service_path in varchar2,
p_service_method in varchar2,p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,p_soap_action in varchar2 := null)
return self as result as
begin
self.service_host := p_service_host;
self.service_path := p_service_path;
self.service_method := p_service_method;
self.service_namespace := nvl(p_service_namespace, 'xmlns="' || p_service_host || '/"');
self.service_url := p_service_host || '/' || p_service_path;
self.soap_namespace := nvl(p_soap_namespace, 'soap');
self.soap_action := nvl(p_soap_action, p_service_host || '/' || p_service_method);
self.envelope := '';
build_env;
return;
end;
member procedure add_param (p_name in varchar2,p_value in varchar2,p_type in varchar2 := null) as
begin
if p_type is null then
m_parameters := m_parameters || chr(13) || ' <' || p_name || '>' || p_value || '</' || p_name || '>';
else
m_parameters := m_parameters || chr(13) || ' <' || p_name || ' xsi:type="' || p_type || '">' || p_value || '</' || p_name || '>';
end if;
build_env;
end add_param;
member procedure add_xml (p_xml in clob) as
begin
m_parameters := m_parameters || chr(13) || p_xml;
build_env;
end add_xml;
member procedure build_env (self in out t_soap_envelope) as
begin
self.envelope := '<' || self.soap_namespace
|| ':Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:'
|| self.soap_namespace || '="http://schemas.xmlsoap.org/soap/envelope/">' ||
'<' || self.soap_namespace || ':Body>' ||
'<' || self.service_method || ' ' || self.service_namespace || '>' ||
self.m_parameters || chr(13) ||
'</' || self.service_method || '>' ||
'</' || self.soap_namespace || ':Body>' ||
'</' || self.soap_namespace || ':Envelope>';
end build_env;
member procedure debug_envelope as i pls_integer;
l_len pls_integer;
begin
if envelope is not null then
i := 1; l_len := length(envelope);
while (i <= l_len) loop
dbms_output.put_line(substr(envelope, i, 200));
i := i + 200;
end loop;
else
dbms_output.put_line ('WARNING: The envelope is empty...');
end if;
end debug_envelope;
end;
/
and it's example:
- Code: Select all
declare
l_env t_soap_envelope;
l_xml xmltype;
l_val varchar2(4000);
l_start_date date;
begin
l_env := t_soap_envelope ('http://www.webserviceX.NET', 'length.asmx', 'ChangeLengthUnit', 'xmlns="http://www.webserviceX.NET/"');
l_env.add_param ('LengthValue', '100');
l_env.add_param ('fromLengthUnit', 'Feet');
l_env.add_param ('toLengthUnit', 'Meters');
l_start_date := sysdate;
l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
l_val := flex_ws_util.get_value (l_xml, 'ChangeLengthUnitResult', l_env.service_namespace, 'error');
--flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
-- p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
dbms_output.put_line('Converting 100 feet to meters ' || l_val);
end;
I don't use a table for record result, but directly i print the solution!
The example work fine. I tested this method with this web service:
http://www.oracle-base.com/webservices/server.php and it work:
- Code: Select all
declare
l_env t_soap_envelope;
l_xml xmltype;
l_val varchar2(4000);
l_start_date date;
begin
l_env := t_soap_envelope ('http://www.oracle-base.com/webservices/server.php', '', 'ws_add', 'xmlns="http://www.oracle-base.com/webservices/"');
l_env.add_param ('int1', '150');
l_env.add_param ('int2', '100');
l_start_date := sysdate;
l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
l_val := flex_ws_util.get_value (l_xml, 'return', null, 'error');
--flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
-- p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
--show_xml(l_xml);
dbms_output.put_line('Somma ' || l_val);
end;
But when i test another web service, for example:
http://www.webservicex.net/globalweather.asmx?WSDL:
- Code: Select all
declare
l_env t_soap_envelope;
l_xml xmltype;
l_val varchar2(4000);
l_start_date date;
begin
l_env := t_soap_envelope ('http://www.webservicex.net', 'globalweather.asmx', 'GetWeather', 'xmlns="http://www.webserviceX.NET/"');
l_env.add_param ('CityName', 'Rome');
l_env.add_param ('CountryName', 'Italy');
l_start_date := sysdate;
l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
l_val := flex_ws_util.get_value (l_xml, 'GetWeatherResponse', l_env.service_namespace, 'error');
--flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
-- p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
--show_xml(l_xml);
dbms_output.put_line('Info: ' || l_val);
end;
i have this response:
- Code: Select all
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><GetWeather xmlns="http://www.webserviceX.NET/">
</GetWeather></soap:Body></soap:Envelope>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><GetWeather xmlns="http://www.webserviceX.NET/">
<CityName>Rome</CityName>
</GetWeather></soap:Body></soap:Envelope>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><GetWeather xmlns="http://www.webserviceX.NET/">
<CityName>Rome</CityName>
<CountryName>Italy</CountryName>
</GetWeather></soap:Body></soap:Envelope>
<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/en
velope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XML
Schema">
<soap:Body>
<soap:Fault>
<faultcode>soap:Client</faultcode>
<faultstring>
System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPA
ction: http://www.webservicex.net/GetWeather.
at System.Web.Services.Protocols.Soap11ServerProtoc
olHelper.RouteRequest()
at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServ
erMessage message)
at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
at System.
Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest requ
est, HttpResponse response, Boolean& abortProcessing)</faultstring>
<detail/>
</soap:F
ault>
</soap:Body>
</soap:Envelope>
Info:
Why this solution don't work with all web service??
Nb.
Naturally, i put spy print (dbms_output.put_line) for understand what i send to web service.
Cheers
