Configure Oracle APEX 3.2.1 using Embedded PL/SQL Gateway (Unsupported Feature)

Just want to share for those who don't want to setup APEX using Apache HTTP Server with mod_plsql (which will be deprecated according to latest SOD Oracle), instead using pre installed Embedded PL/SQL Gateway on top of XML DB Server feature of Oracle Database, but doesn't have the luxury of newest version of Oracle Database (as latest is 11g).

Installation step should be followed normally as documented in installation guide.

After APEX component installation complete (no error occured during installation)
using SQL/Plus Command:

SQL> @apexins SYSAUX SYSAUX TEMP /i/

*) SYSAUX should be replace by another target tablespace created earlier (recommended)

For normal installation it should move to point 3.3.5 Configure the Embedded PL/SQL Gateway but after several times installation experience this always causing error 404 Unauthorized.

I began to examine the instalation script, and found out that there's missing step in creation/configure of DAD (Database Access Descriptor) in:
default installation script => apex_epg_config_core.sql. called by apex_epg_config.sql script.

So the script needs to be patch in several lines after line 345 as the following:

--- Original Script

alter session set current_schema = XDB;

begin
dbms_epg.create_dad('APEX','/apex/*');
dbms_epg.set_dad_attribute('APEX','database-username','ANONYMOUS');
dbms_epg.set_dad_attribute('APEX','default-page','apex');
dbms_epg.set_dad_attribute('APEX','document-table-name','wwv_flow_file_objects$');
dbms_epg.set_dad_attribute('APEX','document-path','docs');
dbms_epg.set_dad_attribute('APEX','nls-language','american_america.al32utf8');
dbms_epg.set_dad_attribute('APEX','document-procedure','wwv_flow_file_mgr.process_download');
dbms_epg.set_dad_attribute('APEX','request-validation-function','wwv_flow_epg_include_modules.authorize');
end;

--- Patched Script

alter session set current_schema = XDB;

begin
dbms_epg.create_dad('APEX','/apex/*');
dbms_epg.authorize_dad (
dad_name => 'APEX',
user     => 'ANONYMOUS'); -- Mesz
dbms_epg.set_dad_attribute('APEX','database-username','ANONYMOUS');
dbms_epg.set_dad_attribute('APEX','default-page','apex');
dbms_epg.set_dad_attribute('APEX','document-table-name','wwv_flow_file_objects$');
dbms_epg.set_dad_attribute('APEX','document-path','docs');
dbms_epg.set_dad_attribute('APEX','nls-language','american_america.al32utf8');
dbms_epg.set_dad_attribute('APEX','document-procedure','wwv_flow_file_mgr.process_download');
dbms_epg.set_dad_attribute('APEX','request-validation-function','wwv_flow_epg_include_modules.authorize');
end;

So you have to rerun EPG configuration using your patch script as describe in installation guide:

On Windows:

SQL> @apex_epg_config SYSTEM_DRIVE:\TEMP

On UNIX and Linux:

SQL> @apex_epg_config /tmp

and then unlock anonymous acount ( the next step) .

*) For detailed explanation of SYSTEM_DRIVE:\TEMP or /tmp please read installation guide.

But it's still will not allowing anonymous access to APEX so the last step required is executing script below (thanks to Oracle-Base)

-- Script to Update Allow Anonymous Acces to XDB
-- For APEX without Apache Instalation
-- MKR, 2009.11.17
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(''
                  || l_value ||
                  ''),
          '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;
/
commit;
/

If the above script give you error:

probably work around alternative script below will solve it.

--Script work around
DECLARE
configxml XMLType;
configxml2 XMLType;
BEGIN
-- Get the current configuration
configxml := DBMS_XDB.cfg_get();

-- Modify the configuration
SELECT INSERTCHILDXML(
configxml,
'/xdbconfig/sysconfig/protocolconfig/httpconfig',
'allow-repository-anonymous-access',
XMLType('true'),
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
INTO configxml2 FROM DUAL;
-- Update the configuration to use the modified version
DBMS_XDB.cfg_update(configxml2);
END;
/
commit;
/
UPDATE resource_view r
SET r.res=UPDATEXML(res, '/a:Resource/a:Contents/b:acl','
xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
xmlns:dav="DAV:"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd"
>

true
ANONYMOUS




',
'xmlns:a="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:b="http://xmlns.oracle.com/xdb/acl.xsd"') WHERE r.any_path = '/sys/acls/ro_anonymous_acl.xml';

After all these unofficial patched step please restart your database.

Hopefully now you can access your APEX home => http://your-host:8080/apex/

Remember that this is not officially supported configuration by Oracle and I think it's not the recommended setup for production site. So the risk is all yours.

I personally used this solution in my Development host only.

Happy patching,
Mesz