Install APEX 4.0 with Embedded PL/SQL Gateway

Oracle® Application Express Release 4.0 Now Available
Time to upgrade APEX on my database:

SQL> SELECT COMP_ID, COMP_NAME, VERSION, STATUS from DBA_REGISTRY WHERE COMP_ID='APEX';

COMP_ID COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ------------------------------ ----------
APEX Oracle Application Express 3.2.1.00.10 VALID

- Download and start to APEX 4.0 (documents):
*** created "APEX" tablespace ***

$ unzip apex_4.0.zip
$ cd apex
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @apexins APEX APEX TEMP /i/

- Change the Password for the ADMIN Account:

SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

*** using Embedded PL/SQL Gateway ***
- Running the apex_epg_config.sql Configuration Script (unzipped source at /tmp ):

SQL> @apex_epg_config /tmp

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:03:00.52

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

- Unlock the ANONYMOUS account:

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

- Update the Images Directory When Upgrading from a Previous Release (nzipped source at /tmp ):

SQL> @apxldimg.sql /tmp

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

timing for: Load Images
Elapsed: 00:02:09.70

Directory dropped.

- Enable Oracle XML DB HTTP Server:

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
0

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> !lsnrctl status | grep HTTP
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxtest01)(PORT=8080))(Presentation=HTTP)(Session=RAW))

- Enable Network Services in Oracle Database 11g:
(Grant connect privileges to any host for the APEX_040000 database user)

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 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_040000'
-- 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_040000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040000', 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_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' 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_040000'
-- 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_040000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040000', TRUE, 'connect');
END IF;

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

- Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:

SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
----------------------------------------------------------------------------------------------------------------------------------
<Not Found>

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;

Grant succeeded.

SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

PL/SQL procedure successfully completed.

SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
----------------------------------------------------------------------------------------------------------------------------------
APEX_URL_DATASTORE_ROLE

- Task for Upgrade:

SQL> SELECT username
FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
AND USERNAME NOT IN (
SELECT 'FLOWS_FILES'
FROM DUAL
UNION
SELECT 'APEX_PUBLIC_USER' FROM DUAL
UNION
SELECT SCHEMA s
FROM dba_registry
WHERE comp_id = 'APEX');

USERNAME
------------------------------
APEX_030200
FLOWS_030000

SQL> DROP USER FLOWS_030000 cascade;

User dropped.

SQL> DROP USER APEX_030200 cascade;

User dropped.

Fix Invalid ACL in Oracle Database 11g:

EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'FLOWS_030000');

Time to test...


Check in DBA_REGISTRY:

SQL> SELECT COMP_ID, COMP_NAME, VERSION, STATUS from DBA_REGISTRY WHERE COMP_ID='APEX';

COMP_ID COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ------------------------------ ----------
APEX Oracle Application Express 4.0.0.00.46 VALID

:)