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