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

28 comments:

Chadders said...

Fantastic. I have wanted to know how to do this for so long. Good spot on the doccos. ;-)

Algis said...

Cool :)

Anonymous said...

well done

Tim Ward said...

I'm not sure I'm following where the package gets created... I've tried it in several schemas but I get the following:

Tue, 22 Feb 2011 15:15:50 GMT

Failed to parse target procedure
myName: PROCEDURE DOESN'T EXIST

DAD name: apex
PROCEDURE : myName
URL : http://1.1.1.1:4443/pls/apex/myName
PARAMETERS :

Morten Braten said...

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

http://1.1.1.1:4443/pls/apex/my_rest_path/this_will_get_passed_to_the_path_alias_procedure

- Morten

Tim Ward said...

I'm still missing something, not sure what but maybe this will help...

Here are the DAD Attributes that are set:

path-alias:my_rest_path
path-alias-procedure:rest_handler.handle_request

and when I try to go to it, I get the following:

Failed to parse target procedure
my_rest_path: PROCEDURE DOESN'T EXIST

DAD name: apex
PROCEDURE : my_rest_path
URL : http://1.1.1.1:4443/pls/apex/my_rest_path/thisisatest

PARAMETERS :
===========

Morten Braten said...

@Tim: What web server are you using? Mod_plsql or DBMS_EPG?

Quoting from my original blog post:

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

- Morten

Tim Ward said...

So, then I have to put it directly into the dads.conf?

For some reason I didn't read that correctly... I'll give it a try.

Scott Wesley said...

I'm a little confused at the applicability of this using a HTTP Server such as Apache.

I'm using Glassfish to serve an 11g instance on my laptop with Apex4.

My typical URL would start as
http://localhost:8080/apex40/f?p=

However when I try to set the dad attributes, I receive this error:
1 begin
2 dbms_epg.set_dad_attribute
3 (dad_name => 'apex40'
4 ,attr_name => 'PlsqlPathAlias'
5 ,attr_value => 'sage');
6* end;
SQL> /
begin
*
ERROR at line 1:
ORA-24231: database access descriptor (DAD) apex40 not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_EPG", line 67
ORA-06512: at "SYS.DBMS_EPG", line 274
ORA-06512: at line 2

Any ideas?

Morten Braten said...

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

- Morten

Scott Wesley said...

Thanks Morten - I was sure that was the case, but I thought maybe I was reading it wrong.

I'll have a play with my Glassfish laptop - but when I define our EC2 instance we will have flexibility.

Thanks - this information is great.

Tim Ward said...

Also for Mod_plsql, I also had to add the following to the dads.conf:

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all

Tim Ward said...

Also had to add this function to get rid of an error I was getting...

-- "Set define off" turns off substitution variables.
Set define off;

CREATE OR REPLACE FUNCTION APEX_030200.wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
--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;
/

Rohitha said...

Hello Oracle Gurus,

Pardon me for my ignorance. I am new to this Web Services thing. I am trying to create a REST WS which handles data in JSON format.

I went through this page. Very interesting but seems pretty complicated to me.

I also bumped into this page http://blog.warp11.nl/2011/07/calling-a-rest-webservice-with-plsql/

which looks very simple.

So, what's the difference between these 2 approaches??? Why is one very long than the other??

Any advantages / disadvantages with these 2 methods (i.e. one over the other)???

Morten Braten said...

@Rohitha: The link you provided is an example of calling (or "consuming") a REST web service on an external server from PL/SQL.

This blog post is about creating (or "exposing") a REST web service from PL/SQL. It's the opposite of calling a service.

Also, the meat of the example is really the handle_request procedure, which is just a dozen or so lines of code.

- Morten

Mike said...

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

Morten Braten said...

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

- Morten

Etay Gudai - Oracle Israel said...

Hello Morten,

1. Thank you for this great Blog.

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 ?

Much appreciated.

Regards
Etay G

Anonymous said...

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;
/

What does this mean if I am running multiple applications?

How do i need to configure the DAD settings so i will get pretty URLs for all applications ?

Do i have to add a line
dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo');

for all applications??
what does rest-demo in this example mean?

Morten Braten said...

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

- Morten

Learco Brizzi said...

Hi Morten,

Do you think this kind of solution works with the latest version of APEX Listener?

Regards,
Learco

Morten Braten said...

@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?

- Morten

Vijay said...

Thanks for sharing this new concept.

I tried to run the same procedure with some table name change and iam able to compile all the procedures without any errors.

When i try to run the URL i am getting page not found error.

I have few questions for you.

1. Do i need to have APEX installed ?
2. I am running all these under system schema and using XE database.


Thanks,
Vijay

Vijay said...

Hi,

How can make it work without using APEX?

Thanks,
Vijay

Morten Braten said...

@Vijay:

1. No, you don't need Apex, this works just using the PL/SQL Web Toolkit (OWA) and the Embedded PL/SQL Gateway (DBMS_EPG).

2. You should use the SYS user to set up the DAD settings, but use another schema (I've used a schema called "devtest") to install your PL/SQL code.

To troubleshoot the "page not found" errors, you should enable the DebugStyle error reporting. See http://daust.blogspot.com/2008/04/troubleshooting-404-not-found-error-on.html for details.

- Morten

another Mikle said...

Hi,

Is it possible to use DAD's parameters:

'PlsqlDocumentTablename',
'PlsqlDocumentPath', ...

for POST,PUT methods?

Thanks,
Mikle

Morten Braten said...

@Mikle: As described in the last part of my post, this will not work for PUT and POST requests, since mod_plsql does not send the posted data to the PathAliasProcedure.

If you use my Thoth Gateway (mod_plsql for IIS), there is a configuration option you can enable to get access to the POST data.

And of course, if you use Oracle's Apex Listener with Apex 4.2, you can use true RESTful Services directly via the listener.

- Morten

Anonymous said...

The link for documentation refers to Oracle 9i. Has anything changed ( advanced) in Oracle 11g? I am on 11g release 2 ( 11.2.0.3)