Application Express Preferences for an APEX Instance

Application Express Preferences is set of vital parameters that describe how Apex Instance beehive. Usually they are set through Apex Administrator GUI interface, but it also may be handy to set them from SQLPlus as well.

How can you determine the APEX preference settings for an APEX instance?

SYS@egis>select name||'=<'||value||'>' from APEX_040000.wwv_flow_platform_prefs order by name;

NAME||'=<'||VALUE||'>'
----------------------------------------------------------------------------------------------------
ACCOUNT_LIFETIME_DAYS=<9999>
ALLOW_DB_MONITOR=<N>
ALLOW_PUBLIC_FILE_UPLOAD=<N>
ALLOW_REST=<N>
AUTOEXTEND_TABLESPACES=<Y>
DISABLE_ADMIN_LOGIN=<N>
DISABLE_WORKSPACE_LOGIN=<N>
DISABLE_WS_PROV=<N>
ENABLE_TRANSACTIONAL_SQL=<Y>
EXPIRE_FND_USER_ACCOUNTS=<N>
MAX_LOGIN_FAILURES=<9999>
MAX_SCRIPT_SIZE=<500000>
MAX_SESSION_IDLE_SEC=<360000>
MAX_SESSION_LENGTH_SEC=<288000>
NOTIFICATION_EMAIL=<damir.vadas@teb-informatika.hr>
PASSWORD_ALPHA_CHARACTERS=<abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ>
PASSWORD_MIN_LENGTH=<0>
PASSWORD_NEW_DIFFERS_BY=<0>
PASSWORD_NOT_LIKE_USERNAME=<N>
PASSWORD_NOT_LIKE_WORDS=<oracle:hello:welcome:guest:user:database>
PASSWORD_NOT_LIKE_WS_NAME=<N>
PASSWORD_ONE_ALPHA=<N>
PASSWORD_ONE_LOWER_CASE=<N>
PASSWORD_ONE_NUMERIC=<N>
PASSWORD_ONE_PUNCTUATION=<N>
PASSWORD_ONE_UPPER_CASE=<N>
PASSWORD_PUNCTUATION_CHARACTERS=<!"#$%&()``*+,-/:;<=>?_>
PLSQL_EDITING=<Y>
PRINT_BIB_LICENSED=<ADVANCED>
PRINT_SVR_HOST=<192.168.168.102>
PRINT_SVR_PORT=<9704>
PRINT_SVR_PROTOCOL=<http>
PRINT_SVR_SCRIPT=</xmlpserver/convert>
REQ_NEW_SCHEMA=<N>
REQUIRE_HTTPS=<N>
SERVICE_REQUEST_FLOW=<MANUAL>
SMTP_FROM=<damir.vadas@teb-informatika.hr>
SMTP_HOST_ADDRESS=<mail.iskon.hr>
SMTP_HOST_PORT=<25>
SQL_SCRIPT_MAX_OUTPUT_SIZE=<200000>
STRONG_SITE_ADMIN_PASSWORD=<N>
WEBSHEET_SQL_ACCESS=<Y>
WORKSPACE_MAX_OUTPUT_SIZE=<2000000>
WORKSPACE_NAME_USER_COOKIE=<Y>
WORKSPACE_PROVISION_DEMO_OBJECTS=<Y>
WORKSPACE_WEBSHEET_OBJECTS=<Y>

46 rows selected.

How to set/reset preferences in a runtime installation?

APEX_INSTANCE_ADMIN.SET_PARAMETER can be used to reset values programatically, if the Administrator GUI interface is not available.
Example.

To reenable the Disable Admin Login preference in APEX 4.0:

1. Login to SQLPLUS as the SYS user.
2. Change users to the APEX schema:
   

   ALTER SESSION SET CURRENT_SCHEMA = APEX_040000;
   

3. Use the APEX_INSTANCE_ADMIN API to reset the preference value for Disable Workspace Login:
   

   BEGIN
      APEX_INSTANCE_ADMIN.SET_PARAMETER('DISABLE_ADMIN_LOGIN', 'N');  
      commit;
   END;
   /
   

The values available for use with APEX_INSTANCE_ADMIN.SET_PARAMETER can be found in the Oracle Application Express API Reference "Available Parameter Values" in the APEX_INSTANCE_ADMIN section.

The End

This post is made more like handy manual but to pretend be be some new and curious Oracle theme.

Cheers!