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

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:

EXECUTE dbms_xdb.SETHTTPPORT(8080);

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

begin

  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

  );

end;



begin

  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 => '127.0.0.1',

  lower_port => 7000,

  upper_port => 9200);

end;

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 :

BEGIN

  DBMS_EPG.create_dad 

  ( dad_name => 'restapi'

  , path => '/api/*'

  );

  DBMS_EPG.AUTHORIZE_DAD('restapi','WC');

end;

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.

SET SERVEROUTPUT ON

DECLARE

  l_configxml XMLTYPE;

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

BEGIN

  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

           (

             l_configxml,

                '/xdbconfig/sysconfig/protocolconfig/httpconfig',

                'allow-repository-anonymous-access',

                XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||

                         l_value ||

                        '</allow-repository-anonymous-access>'),

                'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'

              )

    INTO   l_configxml

    FROM   dual;



    DBMS_OUTPUT.put_line('Element inserted.');

  ELSE

    -- Update existing element.

    SELECT updateXML

           (

             DBMS_XDB.cfg_get(),

             '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',

             l_value,

             'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'

           )

    INTO   l_configxml

    FROM   dual;



    DBMS_OUTPUT.put_line('Element updated.');

  END IF;



  DBMS_XDB.cfg_update(l_configxml);

  DBMS_XDB.cfg_refresh;

END;

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

ALTER USER anonymous ACCOUNT UNLOCK;

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 '{}' 

)

is

begin

  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.

Resources

Some resources:

 http://ora-00001.blogspot.com/2009/07/creating-rest-web-service-with-plsql.html

And especially Tim Hall:

http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php and  http://oracle-base.com/articles/misc/xml-over-http.php

The Oracle documentation: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_web.htm

On debugging and errorpage:  http://daust.blogspot.com/2008/04/troubleshooting-404-not-found-error-on.html