0 
0 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
Related posts:
Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE 
OOW 2009: The killer feature of Oracle Database 11gR2 – Edition Based Redefinition (or database object versioning) How to use an outerjoin to synchronise data 
Jouw toekomst als Oracle professional – AMIS !!EXTRA!! informatieavond over jouw ontwikkelingspad 
Kom kennismaken met AMIS en doe mee met uitdagende projecten
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?
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.
Lucas
how to pass json_payload in movieevents procedure via rest api ?
As the article states:
HTTP POST requests that send a body with any payload to http://localhost:8080/api/movieevents