Getting Apex 4.2 Up and Running on Oracle 12c Using PL/SQL Gateway

Recently I was asked to create a simple Master-Detail Apex form on top of some metadata tables stored within Oracle 12c. Apex version 4.2 is already pre-installed in 12c but it needs configuring to be able to use. This is where I hit some trouble which stems from a conflict of information across a number of different sources.

I was following the Oracle certified documentation on 4.2 and how to get it running on 12c specifically but it wouldn't work. Time after time I kept getting 'Error 404' messages whenever I tried accessing Apex through the browser. I gave up on the Oracle documentation and decided to scout out some blogs but they were virtually copy and paste and resulted in the same error messages. I asked colleagues at work and also joined the Apex slack domain but nobody quite understood why it wasn't working.

It seemed like a lost cause, until I decided to check the later versions of the Oracle documentation and came across an extra piece of information from an Apex 5.0 document:

Procedures CREATE_ACL, ASSIGN_ACL, ADD_PRIVILEGE and CHECK_PRIVILEGE in DBMS_NETWORK_ACL_ADMIN are deprecated in Oracle Database 12c. Oracle recommends using APPEND_HOST_ACE instead.

Given that 12c comes with Apex 4.2, I was surprised to find it not mentioned anywhere else but in the Apex 5.0 documentation.

So the intention for this blog entry, is to bring together all the information that is needed to get Apex 4.2 using PL/SQL Gateway on Oracle 12c so you're able to take a quick and easy look at what it can offer.

Steps to configure the PL/SQL Gateway on Oracle 12c

  1. Change your working directory where Apex is installed:

    cd $ORACLE_HOME/apex  
    
  2. Connect to SQL*Plus with a user with SYS privileges:

    sqlplus / as sysdba  
    
  3. Switch containers to the pluggable database container:

    ALTER SESSION SET CONTAINER = <PDB_NAME>;  
    
  4. Run the apex_epg_config.sql script along with the $ORACLE_HOME:

    @apex_epg_config.sql /u01/app/oracle/product/12.1.0/dbhome_1/
    

    Log back into SQL*Plus from the Apex directory if you get logged out

  5. Then unlock the ANONYMOUS user within the Root Container:

    ALTER SESSION SET CONTAINER = CDB$ROOT;
    ALTER USER ANONYMOUS ACCOUNT UNLOCK;
    
  6. Switch to the pluggable database container and run apxconf.sql:

    ALTER SESSION SET CONTAINER = <PDB_NAME>; 
    @apxconf.sql;
    

    When prompted change the ADMIN password and assign a port for APEX to connect to - Default is 8080.

  7. Verify the Oracle XDB Protocol Server Port that was set after running the apxconf.sql script:

    ALTER SESSION SET CONTAINER = <PDB_NAME>;  
    SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    

    If it returns with [0] then enable the XDB Server

    EXEC DBMS_XDB.SETHTTPPORT(port); COMMIT;  
    
  8. Enable the Network Services in Oracle Database 12c - This will be disabled by default. This following will give access to all hosts:

    BEGIN 
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
    (host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'apex_040200', 
    principal_type => xs_acl.ptype_db)); 
    END; 
    /
    

    NOTE: If you require stricter security add the host name after the host parameter e.g. host => 'localhost'

  9. Open your browser and connect to Apex:

    http://hostname:8080/apex
    

Summary

This blog has given you the guidelines for getting Apex 4.2, that comes pre-installed on Oracle 12c, up and running.

Note that 4.2 is now quite dated and it is recommended to upgrade to Apex 5. This ensures continued support from Oracle, and access to newer and more efficient features.

Subscribe to Rittman Mead

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!

Subscribe to Our
Monthly Newsletter!

* indicates required