EPG

Oracle Embedded PL/SQL Gateway

Enable

To use the Embedded PL/SQL Gateway for APEX...

Assume APEX related variables are set correctly in the .profile.

cd ${APXDIR}/apex

sqlplus / as sysdba @apex_epg_config.sql ${APXDIR}

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

To confirm which HTTP Port to connect to (this port will also support EM Database Express for 12c)...

select DBMS_XDB.GETHTTPPORT from dual;

If it is set to "0", or you need to change the value, use..

The port can be set to any unused port of your choosing, common choices are 8080 or 9090

EXEC DBMS_XDB.sethttpport(8080);

The URL will be of the form...

http://myserver:8080/apex

http://myserver:8080/apex/f?p=4550:1

If the f?p=4550 link fails to work.. are you sure the ADMIN login is enabled?

Disable

To disable HTTP on the Oracle XML DB Protocol Server (e.g. to prevent end user access during an APEX upgrade):

Note the current HTTP Port

select DBMS_XDB_CONFIG.GETHTTPPORT from dual;

EXEC DBMS_XDB.SETHTTPPORT(0); 

Using EPG for something other than APEX

Example

connect / as sysdba

EXEC DBMS_EPG.CREATE_DAD('TEST','/apps/*');

EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('TEST','database-username','SCOTT');

EXEC DBMS_EPG.AUTHORIZE_DAD('TEST','SCOTT');

EXEC DBMS_EPG.AUTHORIZE_DAD('TEST');

connect scott

create or replace procedure hello AS

BEGIN

   htp.htmlopen;                                 -- generates <HTML>

   htp.headopen;                                 -- generates <HEAD>

   htp.title('Hello');                           -- generates <TITLE>Hello</TITLE>

   htp.headclose;                                -- generates </HEAD>

   htp.bodyopen;                                 -- generates <BODY>

   htp.header(1, 'Hello World ' ||SYSTIMESTAMP); -- generates <H1> Hello World </H1>

   htp.bodyclose;                                -- generates </BODY>

   htp.htmlclose;                                -- generates </HTML>

END;

/

If you connect to http://yourserver:8080/apps/hello then you should see a page showing "Hello World" following by a Timestamp.

Troubleshooting

epgstat

This provides a status report that may help pinpoint any configuration issue

@$ORACLE_HOME/rdbms/admin/epgstat

Example output from a working installation...

+--------------------------------------+| XDB protocol ports:                  ||  XDB is listening for the protocol   ||  when the protocol port is non-zero. |+--------------------------------------+
HTTP Port FTP Port--------- --------     8080        0
1 row selected.
+---------------------------+| DAD virtual-path mappings |+---------------------------+
Virtual Path                     DAD Name-------------------------------- --------------------------------/apex/*                          APEX
1 row selected.
+----------------+| DAD attributes |+----------------+
DAD Name     DAD Param                DAD Value------------ ------------------------ ----------------------------------------APEX         database-username        ANONYMOUS             default-page             apex             document-table-name      wwv_flow_file_objects$             request-validation-funct wwv_flow_epg_include_modules.authorize             ion
             document-procedure       wwv_flow_file_mgr.process_download             nls-language             american_america.al32utf8             document-path            docs
7 rows selected.
+---------------------------------------------------+| DAD authorization:                                ||  To use static authentication of a user in a DAD, ||  the DAD must be authorized for the user.         |+---------------------------------------------------+
no rows selected
+----------------------------+| DAD authentication schemes |+----------------------------+
DAD Name             User Name                        Auth Scheme-------------------- -------------------------------- ------------------APEX                 ANONYMOUS                        Anonymous
1 row selected.
+--------------------------------------------------------+| ANONYMOUS user status:                                 ||  To use static or anonymous authentication in any DAD, ||  the ANONYMOUS account must be unlocked.               |+--------------------------------------------------------+
Database User   Status--------------- --------------------ANONYMOUS       EXPIRED
1 row selected.
+-------------------------------------------------------------------+| ANONYMOUS access to XDB repository:                               ||  To allow public access to XDB repository without authentication, ||  ANONYMOUS access to the repository must be allowed.              |+-------------------------------------------------------------------+
Allow repository anonymous access?----------------------------------false

SHARED_SERVERS

This needs to be non-zero in order for XDB to function. XDB is needed by the Embedded PL/SQL Gateway.

SHOW PARAMETER SHARED_SERVERS

If necessary...

alter system set shared_servers = 10 scope=both;

ListenerLocalAccess

This procedure restricts all listener end points of the XML DB HTTP server to listen either only on the localhost interface (when l_access is set to TRUE) or to listen on both localhost and non-localhost interfaces (when l_access is set to FALSE). 

exec dbms_xdb_config.setListenerLocalAccess (l_access => FALSE);

For 11g and earlier use this instead...

exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);

Ensure XDB is registered with the Listener

lsnrctl stat ${LISTENER_NAME} | grep HTTP

Bibliography