I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX.
It’s a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database 🙂

Oracle XE comes with a pre-configured XDB HTTP listener for APEX.
I don’t need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is no need to use PHP or some other scripting engine outside the database.

Here are the steps I used to configure Oracle XE XDB HTTP listener with additional path (DAD) to my custom procedure.

Make HTTP listener to listen on all network interfaces, not only localhost:

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Oracle XE has pre-defined DAD for APEX – /apex/
The following code will create a new DAD /mobileapp/, associate it with schema MOBILEAPP and authorize it to run without asking the user for a password.

BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'mobileapp',
    path     => '/mobileapp/*');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'default-page',
    attr_value => 'home');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'database-username',
    attr_value => 'MOBILEAPP');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'nls-language',
    attr_value => 'american_america.al32utf8');

  DBMS_EPG.authorize_dad (
    dad_name => 'mobileapp',
    user     => 'MOBILEAPP');
END;
/

I used the following DAD attributes:

There are other interesting DAD attributes and the current XDB HTTP configuration and APEX DAD configuration can be views using this SQL:

SQL> SELECT   DBMS_XDB.CFG_GET().getClobVal() FROM DUAL;

...
              
                APEX
                PL/SQL
                APEX
                
                  ANONYMOUS
                  apex
                  wwv_flow_file_objects$
                  docs
                  wwv_flow_file_mgr.process_download
                  american_america.al32utf8
                  wwv_flow_epg_include_modules.authorize
                
              
...

Now all MOBILEAPP PL/SQL procedures and packages can be accessed over HTTP and the HTTP URL has the following form:
http://oraclexeserver:8080/mobileapp/procedurename
or
http://oraclexeserver:8080/mobileapp/packagename.procedurename

So, a simple hello world procedure using PL/SQL Web Toolkit could look like this:

CREATE OR REPLACE PROCEDURE mobileapp.home IS
BEGIN
  HTP.htmlopen;
  HTP.headopen;
  HTP.title('Page title');
  HTP.headclose;
  HTP.bodyopen;
  HTP.print('Hello world');
  HTP.bodyclose;
  HTP.htmlclose;
END;
/

HTTP URL for this procedure will be http://oraclexeserver:8080/mobileapp/home or just http://oraclexeserver:8080/mobileapp/ (note the default-page DAD attribute).
The proceudres like HTP.htmlopen will just generate HTML tags.

Passing HTTP GET parameters is very easy and they will be set as a procedure parameters with the same name. Parameter data type is VARCHAR2 or if HTTP request has more than one parameter with the same name, then TABLE OF VARCHAR2 data type is used.

CREATE OR REPLACE PROCEDURE mobileapp.paramtest(p1 IN VARCHAR2 DEFAULT NULL,
  p2 IN VARCHAR2 DEFAULT NULL) IS
BEGIN
  IF p1 IS NULL THEN
    HTP.p('Hello world');
  ELSE
    HTP.p('Hello p1='||p1||' p2='||p2);
  END IF;
END;
/

This procedure can be called with HTTP URL:
http://oraclexeserver:8080/mobileapp/paramtest?p1=value1&p2=value2
Both parameters are optional (procedure parameters have DEFAULT value set).

If the procedure raises an exception or suitable procedure name with correct argument list cannot be found, then HTTP error code 404 (page not found) is the result.