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.

Sunday, July 5, 2009

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:

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).

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:


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):

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.

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

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? :-)