5.5 Configuring the Embedded PL/SQL Gateway

To configure the embedded PL/SQL gateway, you run a configuration script and unlock the ANONYMOUS account.

The embedded PL/SQL gateway installs with the Oracle Database 11g and later. However, you must configure it before you can use it with Oracle Application Express. To configure the embedded PL/SQL gateway, you run a configuration script and unlock the ANONYMOUS account.

Note:

The Oracle XML DB Protocol Server with the Embedded PL/SQL Gateway is not supported before Oracle Database 11g.

5.5.1 Running the apex_epg_config.sql Configuration Script

Run the apex_epg_config.sql script. Then, unlock the ANONYMOUS account.

You configure the embedded PL/SQL gateway by running the configuration script apex_epg_config.sql. Then, you unlock the ANONYMOUS account.

To run the apex_epg_config.sql configuration script:

WARNING:

This script has to be executed only on the database server . It's not possible to perform a remote connect to the database server. The database server should have access to the specified directory. It's also not possible to map a network directory.

  1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.
  2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  3. If you are configuring a PDB, enter the following statement:
    ALTER SESSION SET CONTAINER = <PDB_NAME>;
    
  4. Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:
    • On Windows:

      @apex_epg_config.sql SYSTEM_DRIVE:\TEMP
      
    • On UNIX and Linux:

      @apex_epg_config.sql /tmp
      
  5. Enter the following statement to unlock the ANONYMOUS account:
    ALTER USER ANONYMOUS ACCOUNT UNLOCK;
    

5.5.2 Updating the Images Directory When Upgrading from a Previous Release

Run the apex_epg_config.sql script to update the images directory.

If you are upgrading Oracle Application Express from a previous release, you must run the apex_epg_config.sql script to update the images directory.

Tip:

If this is a new installation, this step is unnecessary. The images will be loaded by running apex_epg_config.sql as described in the prior section.

To run the apex_epg_config.sql script:

  1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.
  2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  3. Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:
    • On Windows:

      @apex_epg_config.sql SYSTEM_DRIVE:\TEMP
      
    • On UNIX and Linux:

      @apex_epg_config.sql /tmp
      

    Tip:

    The above examples assume that you unzipped Oracle Application Express in a directory called TEMP on Windows and tmp on UNIX or Linux.

5.5.3 Verifying the Port Assigned to HTTP on the Oracle XML DB HTTP Server

Start SQL* Plus and connect to the database and enter the statement to verify the port number assigned to HTTP on the Oracle XML DB HTTP Server.

The embedded PL/SQL gateway runs in the Oracle XML DB Protocol Server in the Oracle database.

To verify the port number assigned to HTTP on the Oracle XML DB HTTP Server:

  1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  2. Enter the following statement to verify the port number:
    SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    

    If the port number returns 0, the Oracle XML DB HTTP Server is disabled.

5.5.4 Verifying the Oracle XML DB Protocol Server Port

Start SQL* Plus and connect to the database and enter the statement to verify the port number where the Oracle XML DB Protocol Server is running.

The Embedded PL/SQL Gateway runs in the Oracle XML DB Protocol Server in the Oracle database. You can determine if the Oracle XML DB Protocol Server is enabled by verifying the associated port number.

To verify the port number where the Oracle XML DB Protocol Server is running:

  1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  2. Enter the following statement to verify the port number:
    SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    

    If the port number returns 0, the Oracle XML DB Protocol Server is disabled.

  3. To enable the Oracle XML DB Protocol Server.

5.5.5 Enabling Oracle XML DB Protocol Server

Start SQL* Plus and connect to the database and run a SQL statement to enable Oracle XML DB Protocol Server.

The embedded PL/SQL gateway runs in the Oracle XML DB Protocol Server in the Oracle database.

To enable Oracle XML DB Protocol Server:

  1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  2. Enter a statement similar to the following:
    EXEC DBMS_XDB.SETHTTPPORT(port);
    

    For example:

    EXEC DBMS_XDB.SETHTTPPORT(8080);
    

Note:

Port numbers less than 1024 are reserved for use by privileged processes on many operating systems. To enable the XML DB HTTP listener on a port less than 1024, such as 80, review the documentation.

See Also:

5.5.6 Configuring Static File Support

For configuring static files, you must run apex_rest_config.sql after a new installation or upgrade of Oracle Application Express.

Oracle Application Express enables application developers to include static files with their applications. Static files can be associated with a workspace, an application, a plug-in, or an application theme. When using the embedded PL/SQL gateway as your web listener, the necessary configuration to support static files is performed as part of the embedded PL/SQL gateway configuration.

5.5.7 About Configuring Embedded PL/SQL Gateway Behind a Reverse Proxy or Load Balancer

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. Execute the PL/SQL block.

If Oracle Application Express is running behind a reverse proxy or load balancer, you set the CGI environment variables PROTOCOL and HTTP_HOST. For embedded PL/SQL gateway, you must call the following PL/SQL block:

  1. Change your working directory to the apex directory where you unzipped the installation software.

  2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

    • On Windows:

      SYSTEM_DRIVE:\ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
    • On UNIX and Linux:

      $ sqlplus /nolog
      SQL> CONNECT SYS as SYSDBA
      Enter password: SYS_password
      
  3. Execute the following PL/SQL block:

    BEGIN
     DBMS_EPG.set_dad_attribute (
     dad_name => 'APEX',
     attr_name => 'cgi-environment-list',
     attr_value => 'REQUEST_PROTOCOL=https');
     DBMS_EPG.set_dad_attribute (
     dad_name => 'APEX',
     attr_name => 'cgi-environment-list',
     attr_value => 'HTTP_HOST=external-name:external-port');
     commit;
    END;
    /
    

To learn more, see your Java EE application servers documentation.