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.

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:

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:

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

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

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.

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

@Tim: You need to set the "path-alias" parameter to the virtual path where you want to accept requests. If you set this to (for example) "my_rest_path", then you can invoke your "path-alias-procedure" using the following syntax:

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

From the "pls" part of your URL, it seems you are using mod_plsql, so then you should use "PlsqlPathAlias" instead of "path-alias".

@Scott: DBMS_EPG is, as the name implies, used to configure the Embedded PL/SQL Gateway (EPG).

If you use Apache with mod_plsql, configuration is done using the .conf file.

If you use the Java-based Apex Listener, then configuration is done using the built-in administration interface. However, I do not believe that the Apex Listener supports the PathAliasProcedure feature (at least not yet -- I suggest you submit an enhancement request to Oracle if you need it).

CREATE OR REPLACE FUNCTION APEX_030200.wwv_flow_epg_include_mod_local( procedure_name in varchar2)return booleanisbegin --return false; -- remove this statement when you modify this function -- -- Administrator note: the procedure_name input parameter may be in the format: -- -- procedure -- schema.procedure -- package.procedure -- schema.package.procedure -- -- If the expected input parameter is a procedure name only, the IN list code shown below -- can be modified to itemize the expected procedure names. Otherwise you must parse the -- procedure_name parameter and replace the simple code below with code that will evaluate -- all of the cases listed above. -- if upper(procedure_name) in ( 'REST_HANDLER.HANDLE_REQUEST') then return TRUE; else return FALSE; end if;end wwv_flow_epg_include_mod_local;/

Great writeup. I have this working as you described. I was wondering if you had any updates on using the POST method. I can currently only post if I include p_path= in the body and it essentially ignores my URL after the alias. Ideally it would use the URL and also allow an XML document in the body without the p_path=.

@Mike: As mentioned at the end of the blog post, "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!"

To my knowledge there has been no change in this regard for mod_plsql (and I haven't checked if/how the Apex Listener handles this), but I did add support for this in my own Thoth Gateway (a mod_plsql port for IIS).

See http://code.google.com/p/thoth-gateway/ and the section called "Features in Thoth that are not in mod_plsql".

2. Small comment (I am not sure about it but it seems like) : Google recognizes this as a redirect call to a different URL (in our case apex application) and lower ranking.

3. I have tried once to implement the following mod_rewrite code, which was very good , except some notification message : https://forums.oracle.com/forums/thread.jspa?threadID=299891&tstart=0&start=15

4. So my questions are : a. Referring to comment mentioned in point 2. b. Are you familiar with any intentions from Oracle to allow native Url friendly built in option ?

@Anonymous: "rest-demo" is the virtual folder name that tells the gateway to forward the request to your handler procedure instead of executing the normal procedure.

"rest_handler.handle_request" is the name of the procedure that will get called.

You can change the names of both of those attributes (making sure to change your code accordingly).

Have a look at the first screenshot in the blog post. The url is

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

In that example, "devtest" is the DAD, "rest-demo" is the prefix that tells the gateway to forward everything after that prefix to the handler procedure, and "employees/7839" is what will actually get forwarded to the handler procedure.

In your code (in the handler procedure) you can parse this string and do anything you like with it. You can make it handle multiple Apex applications if you want.

@Learco: It depends on whether the Apex Listener supports the "PathAlias" and "PathAliasProcedure" configuration parameters. I don't think it does.

However, since the Apex Listener now comes with extensive support for RESTful Web Services, you don't need the PathAliasProcedure for that anymore. Rewriting the URL is another matter, perhaps you can set up a RESTful service in the Apex Listener that executes PL/SQL that issues a redirect to the browser?

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.