Tag: xdb
- Written by: ilmarkerm
- Category: Blog entry
- Published: October 27, 2009
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:
default-page | specifies the default procedure name, that will be used when the procedure name is not specified in a HTTP URL |
---|---|
database-username | associated database schema name |
nls-language | NLS client value, for a web application the important part is the charset. al32utf8 means the result page is in UTF-8. |
DBMS_EPG.authorize_dad | This will disable the HTTP basic authentication |
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.