Using Oracle APEX in an 11g database with the embedded gateway feature

I've used APEX (Oracle Application Express) for a while, since when it was called HTML-DB, and I've written a couple of applications but nothing too involved. In db versions before 11g you had to set up OAS to act as the portal / web service (actually I think 10g had an internal method but it wasn't easy to get going). 11g now has an embedded PL/Sql gateway which doesn't need as much implementation. However, there are some 'gotchas'. Here's some information I've found that I put together on how to get the 11g Embedded Gateway working with APEX:

Application Express 3.2 in 11g Using the embedded gateway


Download the apex32.zip file from Oracle

Extract to a temporary location.

Backup and remove the existing $ORACLE_HOME/apex directory

Copy the new extracted files to the 11g $ORACLE_HOME/apex directory

There are some html help pages in the extracted "install.32/e13366" directory

Run the @apexins script from the $ORACLE_HOME/apex directory:

@apexins tablespace_name tablesspace_name TEMP /i/

tablespace_name = where the objects will be stored


Run the @apxconf script from the $ORACLE_HOME/apex directory as sys

It will prompt for you to create a password for the admin account - note you will
need to change this when you first login from the web page so you might want to make
it a temporary one.

Unlock the anonymous account: ALTER USER ANONYMOUS ACCOUNT UNLOCK;


Check the port : SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If 0, no port is set. It needs to be changed using
   EXEC DBMS_XDB.SETHTTPPORT(nnnn);
   COMMIT;


Go to a web page and enter "http://servername:port/apex/apex_admin

It should show a page with a username/login field.

I had a problem with the login button not doing anything when clicked, this fixed it

@apex_epg_config /u01/app/oracle/product/11.1.0/db_1


Other things to try if things don't work, any or all of these may fix things:


--------
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_030200
  -- the "connect" privilege if APEX_030200 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_030200', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_030200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

---------

ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;

---------

Changing the admin password:

SQL> PASSWORD APEX_PUBLIC_USER

or

SQL> @apxchpwd.sql

---------

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_an
onymous_acl.xml';

---------

lsnrctl reload

---------

alter user xdb account unlock;
alter user xdb identified by xdb;