Jason Straub has written a
Flexible Web Service API package that allows you to call SOAP web services from PL/SQL. The API handles a lot of low-level details for you. As far as I know, the API will be incorporated into the upcoming Apex 4.0.
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.
