How to Manage Users, Preferences, and Caches Using APEX_UTIL

APEX_UTIL is the oldest and largest PL/SQL package in Oracle APEX. Many of its subprograms now have newer homes: session state in APEX_SESSION_STATE, strings in APEX_STRING, and page URLs in APEX_PAGE. It still holds plenty that exists nowhere else, though: the workspace's user accounts and groups, per-user preferences, caches, session settings such as language and time zone, accessibility modes, hashes, and team feedback.

This guide covers the package by task, with tested examples and their real output, and notes the behaviors in APEX 26.1 that the documentation leaves out.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 in a workspace named APEXBOOK, against a test application with ID 200. Run them as the workspace's schema in SQL Developer, SQLcl, or SQL*Plus with server output switched on, and replace the workspace, application, page, and item names with your own. The output under each example is exactly what the database printed.

Examples noted as needing a session expect an APEX session to exist; create one first with APEX_SESSION.CREATE_SESSION for the application, page, and user given, as shown in the guide to creating APEX sessions and managing session state from PL/SQL. The user account examples instead set the workspace with SET_WORKSPACE, as explained further down. The deprecated subprograms of the package, such as STRING_TO_TABLE and IR_FILTER, are not covered.

Session State and Caches

GET_SESSION_STATE and its short form v return an item's value, and GET_NUMERIC_SESSION_STATE and nv return it as a number. SET_SESSION_STATE sets it, and FETCH_APP_ITEM reads an application item of another application or session. The CLEAR procedures clear the session state of one page, of an application, or of the whole session along with its preferences.

Syntax:

apex_util.get_session_state(p_item in varchar2) return varchar2              -- v(p_item)
apex_util.get_numeric_session_state(p_item in varchar2) return number        -- nv(p_item)
apex_util.set_session_state(p_name in varchar2, p_value in varchar2, p_commit in boolean default true)
apex_util.fetch_app_item(p_item in varchar2, p_app in number default null, p_session in number default null) return varchar2
apex_util.clear_page_cache(p_page_id in number default null)
apex_util.clear_app_cache(p_app_id in varchar2 default null)
apex_util.clear_user_cache

This example needs a session of application 200, page 20.

Example:

begin
    apex_util.set_session_state(p_name => 'P20_TEXT', p_value => 'Summit Daypack');
    apex_util.set_session_state('P20_NUMBER', '42.50');
    dbms_output.put_line('get_session_state: ' || apex_util.get_session_state('P20_TEXT'));
    dbms_output.put_line('v:                 ' || v('P20_TEXT'));
    dbms_output.put_line('get_numeric:       ' || apex_util.get_numeric_session_state('P20_NUMBER'));
    dbms_output.put_line('nv:                ' || nv('P20_NUMBER'));
    dbms_output.put_line('fetch_app_item:    ' || nvl(apex_util.fetch_app_item(p_item => 'G_SUPPORT_EMAIL'), '(null)'));
    apex_util.clear_page_cache(p_page_id => 20);          -- the page's items only
    dbms_output.put_line('after clear_page_cache: ' || nvl(v('P20_TEXT'), '(null)'));
    apex_util.set_session_state('P20_TEXT', 'again');
    apex_util.clear_app_cache(p_app_id => 200);             -- all items of the application
    dbms_output.put_line('after clear_app_cache:  ' || nvl(v('P20_TEXT'), '(null)'));
    apex_util.clear_user_cache;                            -- items and preferences of the session
end;
/

Output:

get_session_state: Summit Daypack
v:                 Summit Daypack
get_numeric:       42.5
nv:                42.5
fetch_app_item:    (null)
after clear_page_cache: (null)
after clear_app_cache:  (null)

FETCH_APP_ITEM returned null because the application item G_SUPPORT_EMAIL is filled by a computation that only runs during a page request. For typed values, APEX_SESSION_STATE is the modern choice.

SAVEKEY_VC2, KEYVAL_VC2, SAVEKEY_NUM, and KEYVAL_NUM

Save a value in a package variable and read it back later in the same database call, for example to pass a value from a query's WHERE clause to a function called later in the same statement. The SAVEKEY functions return the value they saved.

Example:

begin
    dbms_output.put_line('savekey_vc2: ' || apex_util.savekey_vc2('ORBIT') || ', keyval_vc2: ' || apex_util.keyval_vc2);
    dbms_output.put_line('savekey_num: ' || apex_util.savekey_num(2282) || ', keyval_num: ' || apex_util.keyval_num);
end;
/

Output:

savekey_vc2: ORBIT, keyval_vc2: ORBIT
savekey_num: 2282, keyval_num: 2282

Page and Region Caches

Pages and regions with Server Cache keep their rendered HTML. These subprograms report when a page or region was cached and purge the caches of an application, a page, or a region, or only the stale ones. APEX_PAGE and APEX_REGION offer the same by static ID.

This example needs a session of application 200, page 1.

Example:

begin
    dbms_output.put_line('page cache:   ' || nvl(to_char(apex_util.cache_get_date_of_page_cache(200, 1)), '(none)'));
    dbms_output.put_line('region cache: ' || nvl(to_char(apex_util.cache_get_date_of_region_cache(200, 1, 'Recent Orders')), '(none)'));
    apex_util.cache_purge_by_page(p_application => 200, p_page => 1);
    apex_util.cache_purge_stale(p_application => 200);
    apex_util.cache_purge_by_application(p_application => 200);
    apex_util.purge_regions_by_name(p_application => 200, p_page => 1, p_region_name => 'Recent Orders');
    apex_util.purge_regions_by_page(p_application => 200, p_page => 1);
    apex_util.purge_regions_by_app(p_application => 200);
    dbms_output.put_line('purged');
end;
/

Output:

page cache:   (none)
region cache: (none)
purged

Calling a cache purge after the data behind a cached region changes, for example at the end of a nightly load, keeps cached pages from showing stale figures. The APEX_PAGE and APEX_REGION versions are covered in the guide to APEX_APPLICATION, APEX_PAGE, and APEX_REGION.

Session Settings

Language, Territory, Time Zone, and Session Length

Set and read the session's language, which decides the translated application the user sees; its territory, which drives number and date formats; and its time zone, which LOCAL TIME ZONE timestamps use. The session length procedures override the application's Maximum Session Length and Maximum Session Idle Time for this session only.

Syntax:

apex_util.set_session_lang(p_lang in varchar2)            apex_util.get_session_lang return varchar2
apex_util.set_session_territory(p_territory in varchar2)  apex_util.get_session_territory return varchar2
apex_util.set_session_time_zone(p_time_zone in varchar2)  apex_util.get_session_time_zone return varchar2
apex_util.set_session_lifetime_seconds(p_seconds in number)
apex_util.set_session_max_idle_seconds(p_seconds in number)

This example needs a session of application 200, page 1.

Example:

begin
    dbms_output.put_line('lang ' || nvl(apex_util.get_session_lang, '(null)') || ', territory ' || nvl(apex_util.get_session_territory, '(null)')
        || ', time zone ' || nvl(apex_util.get_session_time_zone, '(null)'));
    apex_util.set_session_lang(p_lang => 'de');
    apex_util.set_session_territory(p_territory => 'GERMANY');
    apex_util.set_session_time_zone(p_time_zone => 'Europe/Berlin');
    dbms_output.put_line('lang ' || apex_util.get_session_lang || ', territory ' || apex_util.get_session_territory
        || ', time zone ' || apex_util.get_session_time_zone);
    apex_util.set_session_lifetime_seconds(p_seconds => 8 * 3600);   -- this session: at most 8 hours
    apex_util.set_session_max_idle_seconds(p_seconds => 1800);       -- and 30 minutes idle
    dbms_output.put_line('session limits set');
end;
/

Output:

lang (null), territory (null), time zone (null)
lang de, territory GERMANY, time zone Europe/Berlin
session limits set

The getters return null until a value has been set for the session; until then the application's globalization attributes apply. A typical use is setting the language and time zone from the user's profile right after login, as described in the guide to globalization and translation in Oracle APEX.

Screen Reader and High Contrast Modes

Turn the session's screen reader mode and high contrast mode on and off, and check whether they are on. The GET toggle functions return a link that switches the mode, with a message of your choice, and the SHOW toggle procedures write that link to the page. Universal Theme and most regions adapt to these modes.

This example needs a session of application 200, page 1.

Example:

begin
    apex_util.set_session_screen_reader_on;
    apex_util.set_session_high_contrast_on;
    dbms_output.put_line('screen reader: ' || apex_util.is_screen_reader_session_yn
        || ', high contrast: ' || apex_util.is_high_contrast_session_yn);
    dbms_output.put_line('toggle: ' || regexp_replace(apex_util.get_screen_reader_mode_toggle, 'session=\d+', 'session=...'));
    apex_util.set_session_screen_reader_off;
    apex_util.set_session_high_contrast_off;
    dbms_output.put_line('screen reader: ' || case when apex_util.is_screen_reader_session then 'on' else 'off' end
        || ', high contrast: ' || case when apex_util.is_high_contrast_session then 'on' else 'off' end);
    dbms_output.put_line('toggle: ' || regexp_replace(apex_util.get_high_contrast_mode_toggle(p_on_message => 'High contrast'), 'session=\d+', 'session=...'));
end;
/

Output:

screen reader: Y, high contrast: Y
toggle: <a href="/r/apexbook/api-lab/home?request=SET_SESSION_SCREEN_READER_OFF&session=...&cs=3nJoQ4kCBtRo-nItslLw-b9TjRqFsMWNNcsN2JS5nAZRl5HyBhIm7Q75U1TyP9cmeMlUlWItJ0lw9H_vx41AiJQ">Set Screen Reader Mode Off</a>
screen reader: off, high contrast: off
toggle: <a href="/r/apexbook/api-lab/home?request=SET_SESSION_HIGH_CONTRAST_ON&session=...&cs=39r--rmBn1RtpXxueta2ODFCHnG4NvpmkprdftUAv6GIzOTO16C61EDy1gKOsNOT0_D9SI5rLHvB7srOn8NntVw">High contrast</a>

The toggle links carry a checksum, so they work on pages with page access protection. Placing one in a footer or user menu gives users a one-click way to switch modes.

Preferences

SET_PREFERENCE, GET_PREFERENCE, REMOVE_PREFERENCE, and REMOVE_SORT_PREFERENCES

Preferences are per-user values that outlast the session, such as a default store or a chosen layout. APEX stores some of its own there too, such as report sorting, which REMOVE_SORT_PREFERENCES resets. The user defaults to the current one.

Syntax:

apex_util.set_preference(p_preference in varchar2, p_value in varchar2, p_user in varchar2 default null)
apex_util.get_preference(p_preference in varchar2, p_user in varchar2 default v('USER')) return varchar2
apex_util.remove_preference(p_preference in varchar2, p_user in varchar2 default v('USER'))
apex_util.remove_sort_preferences(p_user in varchar2 default v('USER'))

This example needs a session of application 200, page 1.

Example:

begin
    apex_util.set_preference(p_preference => 'ORBIT_DEFAULT_STORE', p_value => '7', p_user => 'ADMIN');
    dbms_output.put_line('get_preference: ' || apex_util.get_preference(p_preference => 'ORBIT_DEFAULT_STORE', p_user => 'ADMIN'));
    apex_util.remove_preference(p_preference => 'ORBIT_DEFAULT_STORE', p_user => 'ADMIN');
    dbms_output.put_line('after remove:   ' || nvl(apex_util.get_preference('ORBIT_DEFAULT_STORE', 'ADMIN'), '(null)'));
    apex_util.remove_sort_preferences(p_user => 'ADMIN');   -- the user's report column sorting
end;
/

Output:

get_preference: 7
after remove:   (null)

Unlike browser storage, a preference follows the user to any device. For per-device conveniences, local storage is simpler, as described in the guide to saving data in the browser with apex.storage.

User Accounts and Groups

APEX_UTIL manages the workspace's APEX accounts: the users of Oracle APEX Accounts authentication and the workspace's developers. The procedures need workspace administrator rights. Inside an application, they also need the application's Security attribute Runtime API Usage to include Modify Workspace Repository, or they raise "An API call has been prohibited". Outside an application, set the workspace with SET_WORKSPACE first, as the examples do. How these accounts are used at login is covered in the guide to authentication schemes, custom login, and sessions.

CREATE_USER, REMOVE_USER, and the User Getters and Setters

CREATE_USER creates an account with its password, names, email, developer privileges, attributes 1 to 10, and account settings. REMOVE_USER removes one by name or ID. The getters read an account by user name; the setters change it by user ID.

Syntax:

apex_util.create_user(p_user_name in varchar2, p_web_password in varchar2, p_email_address in varchar2 default null,
                      p_first_name in varchar2 default null, p_last_name in varchar2 default null,
                      p_developer_privs in varchar2 default null, p_change_password_on_first_use in varchar2 default 'Y',
                      p_account_locked in varchar2 default 'N', p_attribute_01 in varchar2 default null, ...)
apex_util.remove_user(p_user_id in number) | (p_user_name in varchar2)

Example:

declare
    l_id number;
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');     -- outside an application: see the text
    apex_util.create_user(
        p_user_name                    => 'ORBIT_DEMO',
        p_first_name                   => 'Olivia',
        p_last_name                    => 'Demo',
        p_email_address                => 'olivia.demo@orbit-outfitters.example',
        p_web_password                 => 'Orbit#Demo2026',
        p_change_password_on_first_use => 'N',
        p_attribute_01                 => 'STORE-7');
    l_id := apex_util.get_user_id('ORBIT_DEMO');
    dbms_output.put_line('user:  ' || apex_util.get_username(l_id) || ' - ' || apex_util.get_first_name('ORBIT_DEMO')
        || ' ' || apex_util.get_last_name('ORBIT_DEMO') || ' <' || apex_util.get_email('ORBIT_DEMO') || '>');
    dbms_output.put_line('attr1: ' || apex_util.get_attribute('ORBIT_DEMO', 1) || ', roles: ' || nvl(apex_util.get_user_roles('ORBIT_DEMO'), '(end user)'));
    apex_util.set_email(l_id, 'o.demo@orbit-outfitters.example');       -- the setters take the user ID
    apex_util.set_first_name(l_id, 'Liv');
    apex_util.set_last_name(l_id, 'Demo-Smith');
    apex_util.set_attribute(l_id, 2, 'EU');
    apex_util.set_username(l_id, 'ORBIT_DEMO2');
    dbms_output.put_line('now:   ' || apex_util.get_username(l_id) || ' - ' || apex_util.get_first_name('ORBIT_DEMO2')
        || ' ' || apex_util.get_last_name('ORBIT_DEMO2') || ' <' || apex_util.get_email('ORBIT_DEMO2') || '>, attr2 '
        || apex_util.get_attribute('ORBIT_DEMO2', 2));
    dbms_output.put_line('unique ORBIT_DEMO2: ' || case when apex_util.is_username_unique('ORBIT_DEMO2') then 'yes' else 'no, taken' end);
    apex_util.remove_user(p_user_name => 'ORBIT_DEMO2');
    dbms_output.put_line('removed: ' || case when apex_util.is_username_unique('ORBIT_DEMO2') then 'yes' end);
end;
/

Output:

user:  ORBIT_DEMO - Olivia Demo <olivia.demo@orbit-outfitters.example>
attr1: STORE-7, roles: (end user)
now:   ORBIT_DEMO2 - Liv Demo-Smith <o.demo@orbit-outfitters.example>, attr2 EU
unique ORBIT_DEMO2: no, taken
removed: yes

Attributes 1 to 10 are free-form fields you can use for anything, such as a user's home store, and read back in authorization schemes. The example's passwords are test values; never hard-code real ones.

FETCH_USER and EDIT_USER

FETCH_USER returns every property of an account in OUT parameters, with three overloads returning more or fewer of them. EDIT_USER changes them together, including developer privileges such as CREATE:EDIT or ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL.

Example:

declare
    l_email varchar2(240); l_first varchar2(255); l_last varchar2(255); l_roles varchar2(4000);
    l_workspace varchar2(255); l_web_pw varchar2(255); l_desc varchar2(240);
    l_groups varchar2(4000); l_schemas varchar2(4000);
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');     -- outside an application: see the text
    apex_util.create_user(p_user_name => 'ORBIT_DEMO', p_email_address => 'olivia.demo@orbit-outfitters.example',
                          p_web_password => 'Orbit#Demo2026', p_first_name => 'Olivia');
    apex_util.fetch_user(
        p_user_id => apex_util.get_user_id('ORBIT_DEMO'),
        p_workspace => l_workspace, p_user_name => l_desc, p_first_name => l_first, p_last_name => l_last,
        p_web_password => l_web_pw, p_email_address => l_email, p_start_date => l_desc, p_end_date => l_desc,
        p_employee_id => l_desc, p_allow_access_to_schemas => l_schemas, p_person_type => l_desc,
        p_default_schema => l_desc, p_groups => l_groups, p_developer_role => l_roles, p_description => l_desc);
    dbms_output.put_line('workspace ' || l_workspace || ', ' || l_first || ' <' || l_email || '>, roles ' || nvl(l_roles, '(none)'));
    apex_util.edit_user(p_user_id => apex_util.get_user_id('ORBIT_DEMO'), p_user_name => 'ORBIT_DEMO',
                        p_first_name => 'Olivia', p_last_name => 'Demo', p_email_address => l_email,
                        p_developer_roles => 'CREATE:EDIT');       -- make a developer
    dbms_output.put_line('roles after edit_user: ' || apex_util.get_user_roles('ORBIT_DEMO'));
    apex_util.remove_user(p_user_name => 'ORBIT_DEMO');
end;
/

Output:

workspace APEXBOOK, Olivia <olivia.demo@orbit-outfitters.example>, roles (none)
roles after edit_user: CREATE:EDIT

Groups

User groups collect accounts for authorization; an authorization scheme of type Is In Group checks them. CREATE_USER_GROUP and DELETE_USER_GROUP, by name or ID, manage groups. SET_GROUP_USER_GRANTS sets the groups a user belongs to, and SET_GROUP_GROUP_GRANTS the groups a group belongs to. GET_GROUP_ID, GET_GROUP_NAME, and GET_GROUPS_USER_BELONGS_TO read them, and CURRENT_USER_IN_GROUP tests the session's user.

Syntax:

apex_util.create_user_group(p_group_name in varchar2, p_group_desc in varchar2 default null, ...)
apex_util.delete_user_group(p_group_id in number) | (p_group_name in varchar2)
apex_util.set_group_user_grants(p_user_name in varchar2, p_granted_group_names in apex_t_varchar2)
apex_util.set_group_group_grants(p_group_name in varchar2, p_granted_group_names in apex_t_varchar2)
apex_util.current_user_in_group(p_group_name in varchar2) | (p_group_id in number) return boolean

Example:

declare
    l_group_id number;
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');     -- outside an application: see the text
    apex_util.create_user_group(p_group_name => 'ORBIT_BUYERS', p_group_desc => 'Orbit purchasing team');
    apex_util.create_user_group(p_group_name => 'ORBIT_STAFF');
    l_group_id := apex_util.get_group_id('ORBIT_BUYERS');
    dbms_output.put_line('group ' || apex_util.get_group_name(l_group_id) || ' has an ID: ' || case when l_group_id > 0 then 'yes' end);
    apex_util.create_user(p_user_name => 'ORBIT_DEMO', p_web_password => 'Orbit#Demo2026',
                          p_email_address => 'olivia.demo@orbit-outfitters.example');
    apex_util.set_group_user_grants(p_user_name => 'ORBIT_DEMO', p_granted_group_names => apex_t_varchar2('ORBIT_BUYERS'));
    apex_util.set_group_group_grants(p_group_name => 'ORBIT_BUYERS', p_granted_group_names => apex_t_varchar2('ORBIT_STAFF'));
    dbms_output.put_line('ORBIT_DEMO belongs to: ' || apex_util.get_groups_user_belongs_to('ORBIT_DEMO'));
    apex_util.remove_user(p_user_name => 'ORBIT_DEMO');
    apex_util.delete_user_group(p_group_name => 'ORBIT_BUYERS');
    apex_util.delete_user_group(p_group_id => apex_util.get_group_id('ORBIT_STAFF'));
    dbms_output.put_line('groups deleted');
end;
/

Output:

group ORBIT_BUYERS has an ID: yes
ORBIT_DEMO belongs to: ORBIT_BUYERS
groups deleted

Using groups in authorization schemes is covered in the guide to authorization and application security.

Account Status and Passwords

These subprograms lock, expire, and check accounts. End user accounts log into applications; workspace accounts belong to developers and administrators and have their own expiry.

This example runs as two blocks: the second removes the test user in a separate call.

Example:

declare
    procedure yn(p_label varchar2, p_value boolean) is
    begin
        dbms_output.put_line(rpad(p_label, 34) || case when p_value then 'TRUE' else 'FALSE' end);
    end;
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');     -- outside an application: see the text
    apex_util.create_user(p_user_name => 'ORBIT_DEMO', p_web_password => 'Orbit#Demo2026',
                          p_email_address => 'olivia.demo@orbit-outfitters.example', p_change_password_on_first_use => 'N');
    apex_util.lock_account('ORBIT_DEMO');
    yn('locked:', apex_util.get_account_locked_status('ORBIT_DEMO'));
    apex_util.unlock_account('ORBIT_DEMO');
    yn('after unlock_account:', apex_util.get_account_locked_status('ORBIT_DEMO'));
    dbms_output.put_line(rpad('end_user_account_days_left:', 34) || nvl(to_char(apex_util.end_user_account_days_left('ORBIT_DEMO')), '(no expiry)'));
    apex_util.expire_end_user_account('ORBIT_DEMO');
    dbms_output.put_line(rpad('after expire_end_user_account:', 34) || apex_util.end_user_account_days_left('ORBIT_DEMO'));
    apex_util.unexpire_end_user_account('ORBIT_DEMO');
    dbms_output.put_line(rpad('after unexpire_end_user_account:', 34) || nvl(to_char(apex_util.end_user_account_days_left('ORBIT_DEMO')), '(no expiry)'));
    dbms_output.put_line(rpad('workspace_account_days_left:', 34) || nvl(to_char(apex_util.workspace_account_days_left('ORBIT_DEMO')), '(null)'));
    apex_util.expire_workspace_account('ORBIT_DEMO');
    apex_util.unexpire_workspace_account('ORBIT_DEMO');
    yn('change_password_on_first_use:', apex_util.change_password_on_first_use('ORBIT_DEMO'));
    yn('password_first_use_occurred:', apex_util.password_first_use_occurred('ORBIT_DEMO'));
    yn('is_login_password_valid:', apex_util.is_login_password_valid('ORBIT_DEMO', 'Orbit#Demo2026'));
end;
/
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');
    apex_util.remove_user(p_user_id => apex_util.get_user_id('ORBIT_DEMO'));
end;
/

Output:

locked:                           TRUE
after unlock_account:             FALSE
end_user_account_days_left:       45
after expire_end_user_account:    0
after unexpire_end_user_account:  45
workspace_account_days_left:      1
change_password_on_first_use:     FALSE
password_first_use_occurred:      FALSE
is_login_password_valid:          TRUE

The next example resets a password. It starts by removing a user left over from an earlier run, for a reason explained below.

Example:

begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');
    -- start clean: after RESET_PASSWORD, the user cannot be removed in the same database session
    for u in (select user_name from apex_workspace_apex_users
               where workspace_name = 'APEXBOOK' and user_name = 'ORBIT_RESET') loop
        apex_util.remove_user(p_user_id => apex_util.get_user_id(u.user_name));
    end loop;
    apex_util.create_user(p_user_name => 'ORBIT_RESET', p_web_password => 'Orbit#Demo2026',
                          p_email_address => 'reset.demo@orbit-outfitters.example');
    apex_util.reset_password(p_user_name => 'ORBIT_RESET', p_old_password => 'Orbit#Demo2026',
                             p_new_password => 'Orbit#Demo2027', p_change_password_on_first_use => true);
    dbms_output.put_line('new password valid: ' || case when apex_util.is_login_password_valid('ORBIT_RESET', 'Orbit#Demo2027') then 'yes' else 'no' end);
    dbms_output.put_line('old password valid: ' || case when apex_util.is_login_password_valid('ORBIT_RESET', 'Orbit#Demo2026') then 'yes' else 'no' end);
    dbms_output.put_line('must change it:     ' || case when apex_util.change_password_on_first_use('ORBIT_RESET') then 'yes' else 'no' end);
end;
/

Output:

new password valid: yes
old password valid: no
must change it:     yes

In 26.1, a user whose password was just reset cannot be removed in the same database session: REMOVE_USER fails with an internal "Can not delete user" error. That is why the example cleans up a previous run's user at the start instead of at the end.

STRONG_PASSWORD_CHECK and STRONG_PASSWORD_VALIDATION

Check a proposed password against the instance's password complexity rules, or, with p_use_strong_rules, a built-in strong set, before setting it. STRONG_PASSWORD_CHECK returns a Boolean OUT parameter per rule, such as p_min_length_err, p_one_numeric_err, p_one_punctuation_err, p_one_upper_err, p_one_lower_err, p_not_like_username_err, p_not_like_workspace_name_err, p_not_like_words_err, and p_not_reusable_err. STRONG_PASSWORD_VALIDATION returns the failures as HTML, ready for an error message.

This example needs a session of application 200, page 1.

Example:

declare
    l_min_length boolean; l_new_differs boolean; l_one_alpha boolean; l_one_numeric boolean; l_one_punct boolean;
    l_one_upper boolean; l_one_lower boolean; l_not_like_username boolean; l_not_like_ws_name boolean;
    l_not_like_words boolean; l_not_reusable boolean;
    function tf(b boolean) return varchar2 is begin return case when b then 'T' else 'F' end; end;
begin
    apex_util.strong_password_check(
        p_username => 'ORBIT_DEMO', p_password => 'orbit', p_old_password => null, p_workspace_name => 'APEXBOOK',
        p_use_strong_rules => true,                    -- the built-in rules, not the instance's
        p_min_length_err => l_min_length, p_new_differs_by_err => l_new_differs, p_one_alpha_err => l_one_alpha,
        p_one_numeric_err => l_one_numeric, p_one_punctuation_err => l_one_punct, p_one_upper_err => l_one_upper,
        p_one_lower_err => l_one_lower, p_not_like_username_err => l_not_like_username,
        p_not_like_workspace_name_err => l_not_like_ws_name, p_not_like_words_err => l_not_like_words,
        p_not_reusable_err => l_not_reusable);
    dbms_output.put_line('"orbit" fails: min length ' || tf(l_min_length) || ', numeric ' || tf(l_one_numeric)
        || ', punctuation ' || tf(l_one_punct) || ', upper ' || tf(l_one_upper) || ', like user name ' || tf(l_not_like_username));
    dbms_output.put_line(regexp_replace(apex_util.strong_password_validation(
        p_username => 'ORBIT_DEMO', p_password => 'orbit', p_workspace_name => 'APEXBOOK'), '<[^>]+>', ' | '));
end;
/

Output:

"orbit" fails: min length T, numeric T, punctuation T, upper T, like user name F

Under the built-in strong rules, "orbit" fails on length, digits, punctuation, and upper case. The second call checks the instance's own rules instead, and printed nothing here, so none of those rules flagged the password on this instance. Use the strong rules when you want a consistent standard regardless of instance settings.

Workspace and Environment

SET_WORKSPACE, FIND_SECURITY_GROUP_ID, FIND_WORKSPACE, and SET_SECURITY_GROUP_ID

Code outside an application, such as a scheduler job or a script, that calls workspace-scoped APIs such as APEX_MAIL or the user procedures must first set the workspace: by name with SET_WORKSPACE, or by its security group ID with SET_SECURITY_GROUP_ID. FIND_SECURITY_GROUP_ID and FIND_WORKSPACE convert between the two.

Syntax:

apex_util.set_workspace(p_workspace in varchar2)
apex_util.find_security_group_id(p_workspace in varchar2) return number
apex_util.find_workspace(p_security_group_id in varchar2) return varchar2
apex_util.set_security_group_id(p_security_group_id in number)

A few more environment functions:

Example:

declare
    l_sgid number;
begin
    apex_util.set_workspace(p_workspace => 'APEXBOOK');   -- outside an APEX session
    l_sgid := apex_util.find_security_group_id(p_workspace => 'APEXBOOK');
    dbms_output.put_line('security group ID found: ' || case when l_sgid > 0 then 'yes' end);
    dbms_output.put_line('find_workspace:          ' || apex_util.find_workspace(p_security_group_id => l_sgid));
    apex_util.set_security_group_id(p_security_group_id => l_sgid);   -- the same, by ID
    dbms_output.put_line('get_apex_owner:          ' || apex_util.get_apex_owner);
    dbms_output.put_line('get_default_schema:      ' || nvl(apex_util.get_default_schema, '(null: no APEX user)'));
    dbms_output.put_line('get_edition:             ' || nvl(apex_util.get_edition, '(null)'));
    apex_util.close_open_db_links;
end;
/

Output:

security group ID found: yes
find_workspace:          APEXBOOK
get_apex_owner:          APEX_260100
get_default_schema:      ORBIT
get_edition:             (null)

The next example needs a session of application 200, page 1. It simulates a request's CGI variables to call HOST_URL.

Example:

begin
    owa.init_cgi_env(4, owa.vc_arr('REQUEST_PROTOCOL', 'SERVER_NAME', 'SERVER_PORT', 'SCRIPT_NAME'),
                        owa.vc_arr('https', 'apex.orbit-outfitters.example', '443', '/ords'));   -- as in a request
    dbms_output.put_line('host_url:              ' || apex_util.host_url);
    dbms_output.put_line('host_url(SCRIPT):      ' || apex_util.host_url('SCRIPT'));
    dbms_output.put_line('host_url(APEX_PATH):   ' || apex_util.host_url('APEX_PATH'));
    dbms_output.put_line('get_current_user_id:   ' || case when apex_util.get_current_user_id > 0 then '(ADMIN''s ID)' end);
    dbms_output.put_line('get_default_schema:    ' || apex_util.get_default_schema);
end;
/

Output:

host_url:              http://:
host_url(SCRIPT):      http://:/
host_url(APEX_PATH):   http://:/
get_current_user_id:   (ADMIN's ID)
get_default_schema:    ORBIT

HOST_URL builds the URL from the request's CGI variables. In a script there is no real request, and the simulated one lacks what ORDS provides, so the host came back empty. Call it from a page request, or keep the application's URL in an application setting for jobs and emails. Workspace administration more broadly is covered in the guide to workspaces and instance settings.

Authentication Results

A custom authentication function reports why a login failed with SET_AUTHENTICATION_RESULT(p_code), a number of your choice that is logged in the login access log, or with a text of up to 4,000 characters through SET_CUSTOM_AUTH_STATUS(p_status). GET_AUTHENTICATION_RESULT returns the code of the current session.

URLs, Hashes, and Conversions

PREPARE_URL

Adds the checksum that page access protection and item protection require to a URL built as a string, and turns links to dialog pages into the action that opens them. Prefer APEX_PAGE.GET_URL, which builds the whole URL for you.

Syntax:

apex_util.prepare_url(p_url in varchar2, p_url_charset in varchar2 default null, p_checksum_type in varchar2 default null,
                      p_triggering_element in varchar2 default 'this', p_plain_url in boolean default false) return varchar2

This example needs a session of application 200, page 1.

Example:

begin
    dbms_output.put_line(substr(regexp_replace(apex_util.prepare_url(
        'f?p=200:customers:' || v('APP_SESSION') || '::NO:RP:P2_SEARCH:tent'), '\d{12,}', '<session>'), 1, 90) || '...');
    dbms_output.put_line(substr(regexp_replace(apex_util.prepare_url(
        'f?p=200:3:' || v('APP_SESSION') || '::NO:3:P3_CUSTOMER_ID:42'), '\d{12,}', '<session>'), 1, 90) || '...');
end;
/

Output:

/r/apexbook/api-lab/customers?p2_search=tent&clear=RP&session=<session>&cs=14lqPdRSTYjAnsk...
#action$a-dialog-open?url=%2Fr%2Fapexbook%2Fapi-lab%2Fcustomer%3Fp3_customer_id%3D42%26cle...

Two related procedures: REDIRECT_URL(p_url) redirects the browser to a URL and stops the request with STOP_APEX_ENGINE, and COUNT_CLICK, with its short form Z, counts clicks on links to other sites before redirecting to them.

GET_HASH

Computes a hash of a list of values with the instance's algorithm, for a short-lived check value such as a password reset link. By default the hash is salted with the session, so it is only valid there.

Syntax:

apex_util.get_hash(p_values in apex_t_varchar2, p_salted in boolean default true) return varchar2

This example needs a session of application 200, page 1.

Example:

declare
    l_hash varchar2(4000);
begin
    l_hash := apex_util.get_hash(apex_t_varchar2('ORBIT_DEMO', 'reset-password', to_char(sysdate, 'YYYY-MM-DD')));
    dbms_output.put_line('hash: ' || substr(l_hash, 1, 20) || '... (' || length(l_hash) || ' characters)');
    dbms_output.put_line('same input, same hash: ' || case when l_hash = apex_util.get_hash(
        apex_t_varchar2('ORBIT_DEMO', 'reset-password', to_char(sysdate, 'YYYY-MM-DD'))) then 'yes' end);
    dbms_output.put_line('salted per session:    ' || case when l_hash = apex_util.get_hash(
        apex_t_varchar2('ORBIT_DEMO', 'reset-password', to_char(sysdate, 'YYYY-MM-DD')), p_salted => false) then 'no' else 'yes' end);
end;
/

Output:

hash: iToJgd91_8UmgT9n7xZ3... (86 characters)
same input, same hash: yes
salted per session:    yes

GET_SINCE

Returns how long ago, or how far ahead, a date or timestamp is, in words, or in a short form with p_short set to 'Y'. It is the server-side counterpart of apex.date.since, covered in the guide to formatting dates and numbers with apex.date and apex.locale.

Syntax:

apex_util.get_since(p_date in date | timestamp..., p_short in varchar2 default 'N') return varchar2

Example:

begin
    dbms_output.put_line(apex_util.get_since(sysdate - 2));
    dbms_output.put_line(apex_util.get_since(sysdate - 30/1440, p_short => 'Y'));
    dbms_output.put_line(apex_util.get_since(systimestamp + interval '3' hour));   -- TIMESTAMP overload
end;
/

Output:

48 hours ago
30m
3 hours from now

CLOB_TO_BLOB and BLOB_TO_CLOB

Convert between CLOB and BLOB in a given character set, to store text as a file or to read an uploaded text file.

Syntax:

apex_util.clob_to_blob(p_clob in clob, p_charset in varchar2 default null, p_include_bom in varchar2 default 'N') return blob
apex_util.blob_to_clob(p_blob in blob, p_charset in varchar2 default null) return clob

Example:

declare
    l_blob blob;
    l_clob clob;
begin
    l_blob := apex_util.clob_to_blob(p_clob => 'Orbit Outfitters: Grüße aus München', p_charset => 'AL32UTF8');
    dbms_output.put_line('BLOB: ' || dbms_lob.getlength(l_blob) || ' bytes, ' || rawtohex(dbms_lob.substr(l_blob, 6, 1)) || '...');
    l_clob := apex_util.blob_to_clob(p_blob => l_blob, p_charset => 'AL32UTF8');
    dbms_output.put_line('CLOB: ' || l_clob || ' (' || length(l_clob) || ' characters)');
end;
/

Output:

BLOB: 38 bytes, 4F7262697420...
CLOB: Orbit Outfitters: Grüße aus München (35 characters)

The text has 35 characters but 38 bytes, because the German letters take two bytes each in AL32UTF8. Always pass the character set explicitly, so the round trip does not depend on the database's default.

PRN and HTML_PCT_GRAPH_MASK

PRN writes a CLOB to the page's HTP buffer, longer than HTP.P allows, escaped by default. HTML_PCT_GRAPH_MASK returns the HTML of a percentage bar, the same one the PCT_GRAPH format mask of reports renders.

This example needs a session of application 200, page 1. Because it runs outside a web request, it sets up an HTP buffer and reads it back.

Example:

declare
    l_page  htp.htbuf_arr;
    l_lines number := 100;
begin
    owa.init_cgi_env(0, owa.vc_arr(), owa.vc_arr());
    apex_util.prn(p_clob => to_clob('<p>Order ORD-12283 & shipping</p>'), p_escape => true);
    apex_util.prn(p_clob => to_clob('<p>Order ORD-12283</p>'), p_escape => false);
    owa.get_page(l_page, l_lines);
    for i in 1 .. l_lines loop
        if l_page(i) not like 'Content-%' and trim(l_page(i)) is not null then dbms_output.put_line(l_page(i)); end if;
    end loop;
end;
/

Output:

<p>Order ORD-12283 & shipping</p>
<p>Order ORD-12283</p>

Example:

begin
    dbms_output.put_line(replace(apex_util.html_pct_graph_mask(25), '><', '>' || chr(10) || '<'));
end;
/

Output:

<div class="a-Report-percentChart" data-width-chart=100%>
<div role="meter" aria-valuenow="25" aria-label="Percent&#x20;Graph" class="a-Report-percentChart-fill" data-width-fill=25>
</div>
</div>

Files and Supporting Objects

GET_FILE_ID(p_name) returns the ID of a file in the workspace's legacy file repository, GET_FILE(p_file_id, p_inline) downloads it, and GET_BLOB_FILE_SRC(p_item_name, p_v1, ...) returns a download URL for a BLOB column of a form. GET_SUPPORTING_OBJECT_SCRIPT returns the install, upgrade, or deinstall script of an application's supporting objects.

This example needs a session of application 200, page 1. The test application has no supporting objects, so the call fails on purpose.

Example:

declare
    l_script clob;
begin
    l_script := apex_util.get_supporting_object_script(p_application_id => 200, p_script_type => 'INSTALL');
    dbms_output.put_line('install script: ' || dbms_lob.getlength(l_script) || ' characters');
exception
    when others then dbms_output.put_line(substr(sqlerrm, 1, 90));   -- the API Lab has no supporting objects
end;
/

Output:

ORA-20987: APEX - API precondition violated - The requested script does not exist.

Printing

GET_PRINT_DOCUMENT returns a PDF, Word, Excel, HTML, or XML document from report data and an RTF or XSL-FO layout. Four overloads take the data and layout either as values or as names of a report query and report layout in the application. DOWNLOAD_PRINT_DOCUMENT sends the document to the browser. Both need a print server: the instance's Print Server setting, Oracle BI Publisher or a compatible server, or the p_print_server parameter.

Syntax:

apex_util.get_print_document(p_report_data in blob, p_report_layout in clob, p_report_layout_type in varchar2 default 'xsl-fo',
                             p_document_format in varchar2 default 'pdf', p_print_server in varchar2 default null) return blob

This example needs a session of application 200, page 1. The test instance has no print server, so the call fails when it tries to reach one.

Example:

declare
    l_pdf blob;
begin
    l_pdf := apex_util.get_print_document(
        p_report_data        => apex_util.clob_to_blob(
            '<ROWSET><ROW><ORDER_NUMBER>ORD-12283</ORDER_NUMBER></ROW></ROWSET>'),
        p_report_layout      => '<?xml version="1.0"?><xsl:stylesheet version="1.0"'
            || ' xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">'
            || '<xsl:template match="/"><fo:root><fo:layout-master-set><fo:simple-page-master master-name="A4">'
            || '<fo:region-body/></fo:simple-page-master></fo:layout-master-set><fo:page-sequence master-reference="A4">'
            || '<fo:flow flow-name="xsl-region-body"><fo:block><xsl:value-of select="//ORDER_NUMBER"/></fo:block>'
            || '</fo:flow></fo:page-sequence></fo:root></xsl:template></xsl:stylesheet>',
        p_report_layout_type => 'xsl-fo',
        p_document_format    => 'pdf');
    dbms_output.put_line('PDF: ' || dbms_lob.getlength(l_pdf) || ' bytes, starts with '
        || utl_raw.cast_to_varchar2(dbms_lob.substr(l_pdf, 5, 1)));
exception
    when others then dbms_output.put_line(substr(sqlerrm, 1, 90));
end;
/

Output:

ORA-29273: HTTP request failed

For documents without a print server, use APEX_PRINT and the built-in document generator instead, as described in the guide to files, PDF export, and printing in Oracle APEX.

Team Feedback

Applications with the Feedback feature let users send comments from any page. These subprograms submit and manage feedback in code.

This example needs a session of application 200, page 2.

Example:

declare
    l_feedback_id number;
begin
    dbms_output.put_line('feedback enabled: ' || case when apex_util.feedback_enabled then 'yes' else 'no' end);
    apex_util.submit_feedback(
        p_comment         => 'The Customers report should show the loyalty tier.',
        p_type            => 1,                          -- 1 general, 2 enhancement request, 3 bug
        p_application_id  => 200,
        p_page_id         => 2,
        p_email           => 'olivia.demo@orbit-outfitters.example',
        p_rating          => 4);
    select max(feedback_id) into l_feedback_id from apex_team_feedback where application_id = 200;
    apex_util.reply_to_feedback(p_feedback_id => l_feedback_id, p_status => 3,       -- 3: closed
        p_developer_comment => 'Planned for 1.1', p_public_response => 'Added in the next release.');
    apex_util.submit_feedback_followup(p_feedback_id => l_feedback_id, p_follow_up => 'Thank you!');
    dbms_output.put_line('follow up: ' || apex_util.get_feedback_follow_up(p_feedback_id => l_feedback_id, p_row => 1));
    for f in (select feedback, feedback_type, feedback_rating from apex_team_feedback where feedback_id = l_feedback_id) loop
        dbms_output.put_line('stored: "' || f.feedback || '", type ' || f.feedback_type || ', rating ' || f.feedback_rating);
    end loop;
    apex_util.delete_feedback_attachment(p_feedback_id => l_feedback_id);
    apex_util.delete_feedback(p_feedback_id => l_feedback_id);
    dbms_output.put_line('deleted');
end;
/

Output:

feedback enabled: yes
follow up: <br />Now (admin) Thank you!
stored: "The Customers report should show the loyalty tier.", type 1, rating 4
deleted

The APEX_TEAM_FEEDBACK view shows all feedback in the workspace, which makes it easy to build a triage report of your own.

Conclusion

APEX_UTIL reads and clears session state, purges page and region caches, and sets a session's language, territory, time zone, length, and accessibility modes. It keeps per-user preferences that follow users across devices. It manages the workspace's APEX accounts and groups, their passwords and status, either outside an application after SET_WORKSPACE or inside one that allows Modify Workspace Repository. It sets the workspace context for scripts and jobs, prepares URLs with checksums, computes session-salted hashes, converts between CLOB and BLOB, prints through a print server, and manages team feedback. Where a newer package covers the same ground, such as APEX_SESSION_STATE or APEX_PAGE, prefer the newer one.