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.

Creating a REST web service with PL/SQL (and making pretty URLs for your Apex apps)

If you need to expose the data in your Oracle database to other systems in a standardized and technology-neutral way, web services are a natural choice. Broadly speaking, web services come in two flavors: SOAP and REST. Despite its name, the Simple Object Access Protocol (SOAP) can be complex and overkill for many common scenarios. Representational State Transfer (REST) is considered more lightweight and easy to use.

There is a good introduction to REST here: http://www.xfront.com/REST-Web-Services.html

A key point is that REST is not really a standard, but an architectural style. It is not limited to web services consumed by machines, either. Applications built using Ruby on Rails and the ASP.NET MVC framework typically have user-friendly URLs based on the REST principles.

For example, if you have been to StackOverflow.com, you will see URLs like the following, which are "clean" and friendly both to users and to search engines:

?

1 2 3 4 5

http://stackoverflow.com/users http://stackoverflow.com/users/1/jeff-atwood http://stackoverflow.com/questions http://stackoverflow.com/questions/tagged/oracle http://stackoverflow.com/questions/1078506/oracle-sql-developer-how-to-view-results-from-a-ref-cursor

So, REST is a good way of exposing resources on the web, and your Oracle database is full of resources (data), but how can you build a REST service using only PL/SQL?

The key to building a REST service in PL/SQL is in a documented, but little-used feature of the Embedded PL/SQL Gateway (and mod_plsql) called "Path Aliasing". I was not aware that this feature existed until I discovered it "by accident" while browsing the mod_plsql documentation:

http://download.oracle.com/docs/cd/A97335_02/apps.102/a90099/feature.htm#1007126

"If the PL/SQL Gateway encounters in an incoming URL the keyword entered in the Path Alias field, it invokes the procedure entered in the Path Alias Procedure field. (...) Applications that use path aliasing must implement the Path Alias Procedure. The procedure receives the rest of the URL (path_alias_URL) after the keyword, URL, as a single parameter, and is therefore responsible and also fully capable of dereferencing the object from the URL. Although there is no restriction on the name and location for this procedure, it can accept only a single parameter, p_path, with the datatype varchar2."

Sounds good, so let's try it out. First, we need to configure the Database Access Descriptor (DAD) to define a PL/SQL procedure which will handle our REST requests. Using the embedded gateway (DBMS_EPG), the attributes are called "path-alias" and "path-alias-procedure" (the corresponding DAD attributes for mod_plsql are "PlsqlPathAlias" and "PlsqlPathAliasProcedure").

I will be using the embedded gateway for this example. Assuming you have an existing DAD called "devtest", run the following as user SYS (or another user who has the privileges to modify the EPG configuration).

?

1 2 3 4 5 6

begin dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo'); dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias-procedure', attr_value => 'rest_handler.handle_request');   end; /

Then we need to create the procedure itself. Run the following in the schema associated with the DAD:

?

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

create or replace package rest_handler as   /*   Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)   Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure   Who     Date        Description ------  ----------  -------------------------------- MBR     05.07.2009  Created   */   -- the main procedure that will handle all incoming requests procedure handle_request (p_path in varchar2);   end rest_handler; /

And then the package body (the example assumes that the EMP and DEPT demo tables exist in your schema; if not, then modify the code accordingly):

?

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

create or replace package body rest_handler as   /*   Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)   Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure   Who     Date        Description ------  ----------  -------------------------------- MBR     05.07.2009  Created   */     g_request_method_get           constant varchar2(10) := 'GET'; g_request_method_post          constant varchar2(10) := 'POST'; g_request_method_put           constant varchar2(10) := 'PUT'; g_request_method_delete        constant varchar2(10) := 'DELETE';   g_resource_type_employees      constant varchar2(255) := 'employees'; g_resource_type_departments    constant varchar2(255) := 'departments';     procedure handle_emp (p_request_method in varchar2, p_id in number) as begin   /*   Purpose:      Specific handler for Employees   Remarks:   Who     Date        Description ------  ----------  -------------------------------- MBR     05.07.2009  Created   */   if (p_id is not null) then   if p_request_method = g_request_method_delete then   delete from emp where empno = p_id;   elsif p_request_method = g_request_method_get then   for l_rec in (select * from emp where empno = p_id) loop htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal); end loop;   end if;   else   if p_request_method = g_request_method_get then   for l_rec in (select * from emp order by empno) loop htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal || ' '); end loop;   end if;   end if;   end handle_emp;     procedure handle_dept (p_request_method in varchar2, p_id in number) as begin   /*   Purpose:      Specific handler for Departments   Remarks:   Who     Date        Description ------  ----------  -------------------------------- MBR     05.07.2009  Created   */   if (p_id is not null) then   if p_request_method = g_request_method_delete then   delete from dept where deptno = p_id;   elsif p_request_method = g_request_method_get then   for l_rec in (select * from dept where deptno = p_id) loop htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc); end loop;   end if;   else   if p_request_method = g_request_method_get then   for l_rec in (select * from dept order by deptno) loop htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc || ' '); end loop;   end if;   end if;   end handle_dept;     procedure handle_request (p_path in varchar2) as l_request_method constant varchar2(10) := owa_util.get_cgi_env('REQUEST_METHOD'); l_path_elements  apex_application_global.vc_arr2; l_resource       varchar2(2000); l_id             number;   begin   /*   Purpose:      The main procedure that will handle all incoming requests   Remarks:      Parses the incoming path and calls a specific handler for each resource type   Who     Date        Description ------  ----------  -------------------------------- MBR     05.07.2009  Created   */   -- note that an extra delimiter is added to the path, in case the user leaves out the trailing slash l_path_elements := apex_util.string_to_table (p_path || '/', '/');   begin l_resource := l_path_elements(1); l_id := l_path_elements(2); exception when value_error or no_data_found then l_resource := null; l_id := null; end;   case lower(l_resource) when g_resource_type_employees then handle_emp (l_request_method, l_id); when g_resource_type_departments then handle_dept (l_request_method, l_id); when 'apex-employees' then -- we can also use this REST handler to make pretty, search-engine-friendly URLs for Apex applications without having to use Apache mod_rewrite apex_application.g_flow_id := 104; owa_util.redirect_url('http://127.0.0.1:8080/apex/f?p=104:2:' || apex_custom_auth.get_session_id_from_cookie || '::::P2_EMPNO:' || l_id, true); else owa_util.status_line(404, 'Resource type not recognized.', true); end case;   end handle_request;     end rest_handler; /

Now try the following URLs in your browser, and you should be able to see the familiar EMP and DEPT data, in all their RESTful glory! Note that for simplicity, the example code produces simple semicolon-separated values, but depending on your requirements and who will consume the service (machine or human), you will probably want to use XML or HTML as the output format.

?

1 2 3 4

http://127.0.0.1:8080/devtest/rest-demo/departments http://127.0.0.1:8080/devtest/rest-demo/departments/10 http://127.0.0.1:8080/devtest/rest-demo/employees http://127.0.0.1:8080/devtest/rest-demo/employees/7839

Bonus: Making the URLs in your Apex applications pretty


The URLs generated by Apex are not very friendly to users nor to search engines. You can use Apache with mod_rewrite to set up mapping between REST-style URLs to your Apex pages, but the example code above also shows how this can be accomplished using pure PL/SQL.

The example assumes that you have an Apex application with Application ID = 104, and that you have made a Form on the EMP table on Page 2 of the application.

Try the following link

?

1	http://127.0.0.1:8080/devtest/rest-demo/apex-employees/7839

and you should be redirected to the Apex application (and if you were already logged into the Apex application, you don't have to login again as it will reuse your existing session).


REST for inserts and updates
The four HTTP methods are GET, PUT, POST and DELETE. To create a REST web service that can update data as well as query it, we need to inspect the CGI environment variable REQUEST_METHOD and process the request accordingly (see the Wikipedia article for details). The example code implements the GET and DELETE methods for Employees and Departments.

However, I don't see how we can implement POST or PUT processing using the PathAlias technique. The problem is that the webserver/gateway only sends the URL to the PathAliasProcedure. Any data that is POSTed to the URL is simply discarded by the gateway. Ideally, the names and values of the request should be sent to the PathAliasProcedure in name/value arrays (just like the gateway does when using flexible parameter passing). If anyone from Oracle is reading this, it can be considered an enhancement request for the next version of mod_plsql and the embedded gateway!

Even with this limitation, the ability to expose (read-only) data from the database as RESTful web services using just PL/SQL is pretty cool, isn't it? :-)