Follow Us on Twitter

REST-style services using Apex Listener

by Maarten van Luijtelaar on April 8, 2011 · 2 comments

Exposing your PL/SQL procedures to the outside world is nothing new. Using either mod_plsql or dbms_epg one could easily access those procedures through an URL including some parameters to dynamically generate xml, or some other type of resource.

But, if you wanted to provide a true REST-style service you might run out of options. mod_plsql wasn’t designed to do this. It only treats URL’s having parameters, which need to  correspond with your PL/SQL procedure’s parameters. Other than that, there’s no such thing as a “HTTPRequest” in PL/SQL, which means no reading posted data directly.

So, if you want your service to accept raw postdata, the solution is to use Resource Templates which can be configured within the Apex Listener.
When you have the listener up and running, go to the administration site by navigating to http://server:port/apex/listenerAdmin. Here you will find the Resource Templates tab.

The idea here is to add a specific location to your server and configure handlers, which correspond to  HTTP operations like PUT, POST, GET, DELETE.
For instance , specifing an URI template like getsomeresource/{identifier} automatically maps the “identifier” part of the URL as a variable when calling a SQL statement or a PL/SQL block. Keep in mind that none, one, or more of these variables can be specified in the template, but no optional parameters can be configured.

If you need some additional info like the value of the Accept-Language header sent by a browser as part of the request, you can manualy map these to variables by providing a varaiable name and an alias. The alias corresponds with is the actual name of the header you want to capture.  Using this variable you can generate language specific resources.

In our case, we just want to provide a service on a specific location where some data will be posted to for further processing.
So we create an URI template “processdata” with no further variables. The handler will ofcourse be of type POST.
We need to specify a resource generation strategy, in our case a PL/SQL block.
Here we can have direct access to the POST’s content body and content type send by the requestor using special, built-in variables.

:status := pkg_resource.process(:contentType,:body);

Note that the “status” variable must be mapped to a parameter with a built-in alias value X-APEX-STATUS-CODE. This takes care of sending back the actual HTTP status code in the response header like 201: “resource created”. In this case the status code is the return value of the pkg_resource.process function. In case something goes wrong processing, you can return an appropriate code. For a full list of HTTP status codes see :
Also note that there’s no need to map the body and content type variables, it’s been taken care of already for you. The “body” will be passed to the PL/SQL procedure as a blob so you can process the data yourself.

Using Apex Listener’s resource templates allow you to generate content based on SQL queries and PL/SQL blocks, returning csv, custom output and JSON representations, so plenty of options to provide REST-style services to expose your database and make integration with other processes or services easier.

REST-style services using Apex Listener, 4.7 out of 5 based on 3 ratings
VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

{ 2 comments… read them below or add one }

Chris Beaufort June 6, 2012 at 9:37 pm

Hello Maarten,

Your post is very helpful.

I’m hoping that I can trouble you for some related advice (even though I realize that you posted this more that a year ago, so I’m asking you to reach way back into your memory bank!):

1) What “Content-Type” value did you include in your POST request to cause APEX to assign the request body to the “:body” bind variable? (I ask because in the Oracle forums I saw a post that says a “Content-Type” value of “application/x-www-form-urlencoded” will cause APEX to treat the request body as a form and convert each field in the form into a bind variable). My request body is a XML document and I want it stuffed, in its entirety, into the “:body” variable.

2) What variable type(s) did you define in your “pkg_resource.process()” function to receive the “:contentType” and “:body” bind variable values, “varchar2”?



Maarten van Luijtelaar June 14, 2012 at 2:52 pm

Hi Chris,

Thanks for your question.
Since you want to POST an xml document, you may want to set your POST-operations’s Content-Type to “text/xml”.
And you’re right, according to the documentation, setting Content-Type to “application/x-www-form-urlencoded” will map the form fields to the corresponding bind variable as input parameters of the PL/SQL block.

As far as I can remember, the contentType parameter can be declared as a VARCHAR2, the body should be a BLOB.

Is your question by any chance related to




Leave a Comment


Previous post:

Next post:

About Whitehorses
Company profile

Whitehorses website

Home page

Follow us
Blog post RSS
Comment RSS