EPG

Oracle Embedded PL/SQL Gateway

Coding

Oracle APEX/REST

Enable

Disable

Using EPG for something other than APEX

Ensure XDB is registered with the Listener

Bibliography

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

APEX ADMIN Login

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

Dedicated / Shared Server and XDB (Doc ID 1396825.1)Enable Logging and Debugging for the Embedded PL/SQL HTTP Gateway (EPG) (Doc ID 563704.1)https://community.oracle.com/thread/960611https://docs.oracle.com/cd/E71588_01/HTMIG/configuring-embedded-PL-SQL-gateway.htm#HTMIG29203
https://docs.oracle.com/en/database/oracle/oracle-database/20/arpls/DBMS_XDB_CONFIG.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XDB_CONFIG.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_XDB_CONFIG.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_XDB_CONFIG.htmlhttps://docs.oracle.com/database/121/ARPLS/d_xdb_admin.htm
https://stackoverflow.com/questions/49701004/oracle-apex-admin-login-issue