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.

?

1 2 3 4 5 6 7 8 9 10 11 12

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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.