This is a living document with my personal notes around APEX.

Usual Editors for PL/SQL Development

Unusual Editors for PL/SQL Development

Update APEX XE Dev Instance

My usual steps in a fresh Docker container with 11G XE - I don’t need to install the images into the EPG because I use a Node.js based proxy server in front of the Docker container which also hosts the images:

-- pimp EPG a little bit
alter system set shared_servers=10 scope=both;
alter system set max_shared_servers=20 scope=both;
alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=xeXDB) (DISPATCHERS=2)' scope=both;

-- unlock APEX public user
alter user apex_public_user account unlock;

-- install APEX
@/Users/ottmar/Documents/nodejs-proxy/apex510en/apexins.sql SYSAUX SYSAUX TEMP /i/;

-- change password of instance admin (e.g. Oracle1! because of complexity rules)
@/Users/ottmar/Documents/nodejs-proxy/apex510en/apxchpwd.sql;

-- login into internal workspace and change security settings & passwords
<a href="f?p=1_app_id:
             2_app_page_id:
             3_session:
             4_request:
             5_debug:
             6_clear_cache:
             7_item_names:
             8_item_values:
             9_printer_friendly">

A common example:

<a href="f?p=&APP_ID.:123:&SESSION.:::123:item1,item2:value1,value2">

Predifined Variables

  • APP_USER
  • APP_ID
  • APP_PAGE_ID
  • APP_UNIQUE_PAGE_ID
  • APP_SESSION or SESSION
  • APP_VERSION
  • APP_IMAGES
  • WORKSPACE_IMAGES
  • IMAGE_PREFIX

  • REQUEST
  • DEBUG
  • PRINTER_FRIENDLY

  • BROWSER_LANGUAGE
  • PUBLIC_URL_PREFIX
  • AUTHENTICATED_URL_PREFIX
  • HOME_LINK
  • LOGIN_URL
  • LOGOUT_URL
  • CURRENT_PARENT_TAB_TEXT
  • PROXY_SERVER
  • SQLERRM
  • SYSDATE_YYYYMMDD

  • #SQLERRM# (error message page process)
  • #TIME# (timing information report footer)

Usage examples:

  • In a region: Hello &APP_USER. - Welcome to Application Express
  • In PL/SQL: htp.p('You are logged in as: ' || V('APP_USER'));
  • As bind variable: SELECT * FROM tab WHERE user_id = :APP_USER