The FLEX_WS_API package is very useful; however, there are still a few things, such as constructing the SOAP envelope and logging requests and response for debugging purposes, that you need to implement yourself.
Here are a few helpers that I have written to do just that:
Web service log table
First, let's make a table that can be used to log web service requests and responses.
create table ws_log ( request_start_date date, request_end_date date default sysdate, log_text varchar2(4000), ws_url varchar2(4000), ws_method varchar2(4000), ws_request clob, ws_response clob, val1 varchar2(4000), val2 varchar2(4000), val3 varchar2(4000) );
Web service utility package
Here is the header of a package that handles logging, and also simplifies the extraction of values from the web service response.
create or replace package flex_ws_util as /* Purpose: The package is a companion to the flex_ws_api package Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ -- 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; /
And then the package body:
create or replace package body flex_ws_util as /* Purpose: The package is a companion to the flex_ws_api package Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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 Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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 Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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 Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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 Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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 Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ 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; /
PL/SQL object type for SOAP envelopes
The following object type is used to simplify creation of SOAP envelopes to be used in web service calls:
create or replace TYPE t_soap_envelope AS OBJECT ( /* Purpose: Object type to handle SOAP envelopes for web service calls Remarks: Who Date Description ------ ---------- ------------------------------------- MBR 17.02.2009 Created */ -- 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 ); /
The type body is implemented like this:
create or replace type body t_soap_envelope
as
/*
Purpose: Object type to handle SOAP envelopes for web service calls
Remarks:
Who Date Description
------ ---------- -------------------------------------
MBR 17.02.2009 Created
*/
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;
/
Example of use
With the above objects created in your database, the code for calling a web service and extracting and logging the results now becomes simple and elegant like this:
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);
end;
If you have complex parameters that you need to add to the request, you can use the add_xml member procedure of the t_soap_envelope type to add any content to the envelope.
