Publish a REST service from PL/SQL to handle HTTP POST requests – using the embedded PL/SQL gateway

Lucas Jellema 4

Oracle Database can act as an HTTP server – using the Embedded PL/SQL Gateway (the 10g successor of the MOD_PLSQL gateway). With just a few statements, we can have the Oracle Database become a listener to HTTP requests (GET or POST). When requests are received at the configured host, port and URL, the request is passed to a PL/SQL procedure that handles it and prepares a response.

In this article, we will expose a REST service at URL http://localhost:8080/api/movieevents. This service processes an HTTP POST request that in this case contains a JSON payload. The payload is passed to the PL/SQL procedure to do with as it feels fit.

The implementation takes place in two steps. First, some preparations must be made by the DBA – to make it possible for a particular database schema to handle HTTP requests received on a certain URL. This includes opening up a certain host and port.

First, you may want to set the HTTP port:

select dbms_xdb.gethttpport
from   dual

and if you do not like it, set another one:


The following statements create the Access Control List that specifies that connection is allowed to database schema WC with HTTP requests to host (aka localhost) and ports between 7000 and 9200:


  dbms_network_acl_admin.create_acl (

    acl             => 'utlpkg.xml',

    description     => 'Normal Access',

    principal       => 'CONNECT',

    is_grant        => TRUE,

    privilege       => 'connect',

    start_date      => null,

    end_date        => null




  dbms_network_acl_admin.add_privilege ( 

  acl         => 'utlpkg.xml',

  principal     => 'WC',

  is_grant     => TRUE, 

  privilege     => 'connect', 

  start_date     => null, 

  end_date     => null); 

  dbms_network_acl_admin.assign_acl (

  acl => 'utlpkg.xml',

  host => '',

  lower_port => 7000,

  upper_port => 9200);


Next, the DAD is created – linking the URL path segment /api/ to the WC database schema. This means that any HTTP request received at http://localhost:8080/api/XXX is passed to a PL/SQL procedure called XXX :



  ( dad_name => 'restapi'

  , path => '/api/*'




The next line instructs the Embedded PL/SQL Gateway to return a readable error page whenever a request is not processed correctly:

exec dbms_epg.set_dad_attribute('restapi', 'error-style', 'DebugStyle');

This line associates the database user WC with the restapi url.

EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('restapi', 'database-username', 'WC');

The final aspect of the preparation involves allowing anonymous access – this means that no username and password are required for HTTP calls  handled by the Embedded PL/SQL Gateway. As per Tim Hall’s instructions:

to enable anonymous access to the XML DB repository, the following code creates the “<allow-repository-anonymous-access>” element if it is missing, or updates it if it is already present in the xdbconfig.xml file.



  l_configxml XMLTYPE;

  l_value     VARCHAR2(5) := 'true'; -- (true/false)


  l_configxml := DBMS_XDB.cfg_get();

  IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN

    -- Add missing element.

    SELECT insertChildXML





                XMLType('<allow-repository-anonymous-access xmlns="">' ||

                         l_value ||




    INTO   l_configxml

    FROM   dual;

    DBMS_OUTPUT.put_line('Element inserted.');


    -- Update existing element.

    SELECT updateXML







    INTO   l_configxml

    FROM   dual;

    DBMS_OUTPUT.put_line('Element updated.');





The database account anonymous also has to be unlocked to truly enable anonymous access:



This completes the preparations. We now have setup a DAD that is associated with the /api/* path in HTTP requests sent to http://localhost:8080/api/*. This DAD hands requests to the WC database schema to be handled. Requests do not have to include username and password.

Now we have to connect to the WC database schema in order to create the PL/SQL procedure that will handle such requests.

create or replace procedure movieevents

( p_json_payload in varchar2 default '{}' 




  htp.p('call received p_json_payload='||p_json_payload);

  htp.p('REQUEST_METHOD='||owa_util.get_cgi_env(param_name => 'REQUEST_METHOD'));

end movieevents;

Between the definition of the DAD, the opening up of the port range and the creation of this procedure, we have completed the setup that will receive and process HTTP POST requests that send a body with any payload to http://localhost:8080/api/movieevents. This call will result in nothing but a simple response that describes in plain text what it received.

This opens up a bridge from any client capable of speaking HTTP to the Database – non transactional, cross firewall and without additional drivers.


Some resources:

And especially Tim Hall: and

The Oracle documentation:

On debugging and errorpage:

4 thoughts on “Publish a REST service from PL/SQL to handle HTTP POST requests – using the embedded PL/SQL gateway

  1. Hi Lucas, Have you ever tried HTTP PUT or DELETE in REST Service with PL/SQL? If successfully implemented, would you share the sample code?

    1. I am not sure I have ever done that – I probably did not. I am not sure whether it is supported. Sorry I can’t be of more help.

Comments are closed.

Next Post

Java Web Application sending JSON messages through WebSocket to HTML5 browser application for real time push

This article describes a Java EE 7 web application that exposes a REST service that handles HTTP POST requests with JSON payload. Any message received is sent through a Web Socket to the web socket (server) endpoint that is published by a Java Class deployed as part of the web […]
%d bloggers like this: