Initialize Oracle APEX (HTMLDB) session state for debugging

If you ever wanted to debug your code written for Oracle Application Express (HTMLDB), you probably run into problem when using function V(...) to access user session variables. To make code runnable outside of APEX environment you should not reference APEX session state, but this is not always easy and sometimes really impossible.

In my application I use many views referencing function V() and also some of my procedures use this function, but it is very hard to debug such procedure or view, because session state is not accessible and so function V() always returns NULL.

After some time of research I found very easy way how to create your debug environment with any session state as you wish.

Key to success is package wwv_flow. Inside this package are all variables containing your session state. They have names like g_user, g_instance etc. As you see they do not correspond to names in application where are names like APP_USER, APP_SESSION etc.

These are so called built-in variables, but what to do to initialize your own variable. There are two arrays g_substitution_item_name and g_substitution_item_value for this purpose. g_substitution_item_name contains names of your variables and g_substitution_item_value contains values on their corresponding index.

Below is small example how to initialize your session before debugging outside APEX.

wwv_flow.g_user := 'APP_USER'; -- V('APP_USER')
wwv_flow.g_instance := 12345678901234; -- V('APP_SESSION')
wwv_flow.g_flow_id := 101; -- V('APP_ID')
wwv_flow.g_flow_step_id := 1; -- V('APP_PAGE_ID')

wwv_flow.g_substitution_item_name(1) := 'ITEM_NAME';
wwv_flow.g_substitution_item_value(1) := 'ITEM_VALUE';
wwv_flow.g_substitution_item_name(2) := 'P1_ITEM';
wwv_flow.g_substitution_item_value(2) := 'P1_ITEM_VALUE';


Complete set of variables in wwv_flow:

FLOW_SESSION	wwv_flow.g_instance
FLOW_ID	wwv_flow.g_flow_id
FLOW_ALIAS	wwv_flow.g_flow_alias
FLOW_USER	wwv_flow.g_user
FLOW_USER_ID	wwv_flow.g_user_id
FLOW_STEP_ID	wwv_flow.g_flow_step_id
FLOW_PAGE_ID	wwv_flow.g_flow_step_id
FLOW_CURRENT_MIN_ROW	wwv_flow.g_flow_current_min_row
FLOW_CURRENT_MAX_ROWS	wwv_flow.g_flow_current_max_rows
FLOW_CURRENT_ROWS_FETCHED	wwv_flow.g_flow_current_rows_fetched
FLOW_LAST_TAB_PRESSED	wwv_flow.g_last_tab_pressed
APP_SESSION	wwv_flow.g_instance
APP_ID	wwv_flow.g_flow_id
APP_ALIAS	wwv_flow.g_flow_alias
APP_USER	wwv_flow.g_user
APP_USER_ID	wwv_flow.g_user_id
APP_PAGE_ID	wwv_flow.g_flow_step_id
APP_CURRENT_MIN_ROW	wwv_flow.g_flow_current_min_row
APP_CURRENT_MAX_ROWS	wwv_flow.g_flow_current_max_rows
APP_CURRENT_ROWS_FETCHED	wwv_flow.g_flow_current_rows_fetched
APP_LAST_TAB_PRESSED	wwv_flow.g_last_tab_pressed
APP_UNIQUE_PAGE_ID	wwv_flow.g_unique_page_id
APP_TRANSLATION_ID	wwv_flow.g_translated_flow_id
APP_TRANSLATION_PAGE_ID	wwv_flow.g_translated_page_id
REQUEST	wwv_flow.g_request
USER	wwv_flow.g_user
USER_KNOWN_AS	wwv_flow.g_user_known_as
SESSION	wwv_flow.g_instance
INSTANCE	wwv_flow.g_instance
PUBLIC_URL_PREFIX	wwv_flow.g_public_url_prefix
AUTHENTICATED_URL_PREFIX	wwv_flow.g_dbauth_url_prefix
PUBLIC_USER	wwv_flow.g_public_user
AUTHENTICATION	wwv_flow.g_authentication
POPUP_FILTER	wwv_flow.g_popup_filter
CURRENT_PARENT_TAB_TEXT	wwv_flow.g_current_parent_tab_text
OWNER	wwv_flow.g_flow_owner
MAP1_X	wwv_flow.g_map1(1)
MAP1_Y	wwv_flow.g_map1(2)
MAP2_X	wwv_flow.g_map2(1)
MAP2_Y	wwv_flow.g_map2(2)
MAP3_X	wwv_flow.g_map3(1)
MAP3_Y	wwv_flow.g_map3(2)

Some (maybe) useful variables outside wwv_flow:

FLOW_JOB	wwv_flow_plsql_job.g_job
APP_JOB	wwv_flow_plsql_job.g_job
FLOW_SECURITY_GROUP_ID	wwv_flow_security.g_security_group_id
APP_SECURITY_GROUP_ID	wwv_flow_security.g_security_group_id
COMPANY_ID	wwv_flow_security.g_security_group_id
WORKSPACE_ID	wwv_flow_security.g_security_group_id