Tuesday, July 14, 2009

Calling a SOAP web service from PL/SQL by extending the FLEX_WS_API

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.

6 comments:

Anonymous said...

How does this differ from the built-in utl_dbws package?

http://www.oracle-base.com/articles/10g/utl_dbws10g.php

Morten Braten said...

@Anonymous:

The main difference is that the utl_dbws package is a wrapper for Java code, while the flex_ws_api package and my companion t_soap_envelope type are written in PL/SQL.

- Morten

Richard said...

Thanks for this, it works brilliantly. I have implemented it and now use it with a user validation solution (a kind of 'SSO-lite').

When the response node contains single instances of each sub-node it works fine. However, one of my web services returns sub-node values that contain repeated sub-nodes i.e. an array of values
e.g.








At the moment, flex_ws_util.get_value() returns null when I pass it 'ApplicationsResult' as p_name, rather than the ' values? (at the moment I parse the entire returned XML using TABLE(XMLSEQUENCE(EXTRACT( )

Thanks again,
RIchard

Richard said...

hmmm, it looks like my xml got hidden by the browser - here it is again using [] instead of < >
---->
When the response node contains single instances of each sub-node it works fine. However, one of my web services returns sub-node values that contain repeated sub-nodes i.e. an array of values
e.g.
[ApplicationsReponse]
[ApplicationsResult]
[string][/string]
[string][/string]
[string][/string]
[/ApplicationsResult]
[/ApplicationsReponse]

At the moment, flex_ws_util.get_value() returns null when I pass it 'ApplicationsResult' as p_name, rather than the '[string][/string][str...' value as I'd expect.

How do I extract the [string] values? (at the moment I parse the entire returned XML using TABLE(XMLSEQUENCE(EXTRACT( )

Piotr Chabot Stadhouders said...

Dear mr Braten,

Could please help me out. I am trying to use the FLEX_WS_API for calling a SOAP service using certificates. I have created a wallet using the wallet manager. However, I have to deal with a root certificate and a system certificate. These certificates are used for authentication. When using the ewallet.p12 in the tool soapui I get a valid response. But when using the command UTL_HTTP.set_wallet and then other UTL_HTTP commands I get "validation certificate failed". I am using oracle 10.2 on Windows. Can you please help me out?

Morten Braten said...

@Piotr: Do you have Apex 4 installed in your database? If so, please try out APEX_WEB_SERVICE.MAKE_REQUEST and pass the p_wallet_path and p_wallet_pwd parameters to do a call to an https site.

See http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_web_service.htm#BABEEICD for more info.

- Morten