How to Read Application, Page, and Region Details in PL/SQL Using APEX_APPLICATION and APEX_PAGE

PL/SQL code in Oracle APEX often needs to know where it is running: which application, page, and user, what the request was, or what a report on the page is currently showing. It also needs to build links, reset reports, change settings, and switch theme styles. Five packages cover that context: APEX_APPLICATION for the request's global variables, APEX_PAGE for URLs and page modes, APEX_REGION for regions and their data, APEX_APP_SETTING for application settings, and APEX_THEME for theme styles.

This guide covers all five with tested examples and their real output. Highlights include building URLs with checksums and dialog links, reading and exporting a report's data exactly as the user sees it, and changing theme styles for one user, one session, or everyone.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200, whose page 2 has a customers interactive report with the static ID customers and whose page 3 is a modal customer dialog. The output under each example is exactly what the database printed.

Inside an application, in a process, computation, or validation, the context already exists and you can use these packages directly. Outside one, every example needs an APEX session: create one first with APEX_SESSION.CREATE_SESSION for the application, page, and user noted above each example, as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Run the examples as the application's parsing schema in SQL Developer, SQLcl, or SQL*Plus with server output switched on, and replace the application ID, pages, and static IDs with your own.

Request Context: APEX_APPLICATION

APEX_APPLICATION is the package of the APEX engine itself. Its global variables describe the current request: g_flow_id (the application ID), g_flow_step_id (the page), g_instance (the session), g_user, g_flow_alias, g_request, g_debug, g_date_format, and more. g_x01 to g_x10 and g_f01 to g_f50 carry the parameters of Ajax calls. In SQL, prefer the bind variables :APP_ID, :APP_USER, and so on; in PL/SQL, these variables.

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

Example:

begin
    dbms_output.put_line('g_flow_id:      ' || apex_application.g_flow_id);
    dbms_output.put_line('g_flow_step_id: ' || apex_application.g_flow_step_id);
    dbms_output.put_line('g_user:         ' || apex_application.g_user);
    dbms_output.put_line('g_instance:     ' || case when apex_application.g_instance is not null then '(the session ID)' end);
    dbms_output.put_line('g_flow_alias:   ' || apex_application.g_flow_alias);
    dbms_output.put_line('g_debug:        ' || case when apex_application.g_debug then 'on' else 'off' end);
    dbms_output.put_line('g_request:      ' || nvl(apex_application.g_request, '(null)'));
    dbms_output.put_line('g_date_format:  ' || apex_application.g_date_format);
end;
/

Output:

g_flow_id:      200
g_flow_step_id: 20
g_user:         ADMIN
g_instance:     (the session ID)
g_flow_alias:   API-LAB
g_debug:        off
g_request:      (null)
g_date_format:  DS

HELP

Writes the help text of a page, its Help Text attribute, and of its items as HTML. It is meant for a help page of your own: a PL/SQL Dynamic Content region that calls it with the page of the request.

Syntax:

apex_application.help(
    p_flow_id            in varchar2 default null,
    p_flow_step_id       in varchar2 default null,
    p_show_item_help     in varchar2 default 'YES',
    p_show_regions       in varchar2 default 'YES',
    p_before_page_html   in varchar2 default '<p>',  ... )

The remaining parameters wrap the page help, regions, item labels, and item help in HTML of your own.

This example needs a session of application 200, page 1. Because it runs outside a web request, it sets up an HTP buffer first and then reads the HTML back from it, the same buffer a region writes to the page.

Example:

declare
    l_page  htp.htbuf_arr;
    l_lines number := 1000;
    l_html  varchar2(32767);
begin
    owa.init_cgi_env(0, owa.vc_arr(), owa.vc_arr());         -- an HTP buffer outside a web request
    apex_application.help(p_flow_id => 200, p_flow_step_id => 2, p_show_regions => 'NO');
    owa.get_page(l_page, l_lines);
    for i in 1 .. l_lines loop
        if l_page(i) not like 'Content-%' then                   -- skip the HTTP headers
            l_html := l_html || l_page(i);
        end if;
    end loop;
    for r in (select column_value as para from apex_string.split(l_html, '</p>') where trim(column_value) is not null) loop
        dbms_output.put_line(substr(trim(r.para), 1, 88) || '...');     -- the start of each paragraph
    end loop;
end;
/

Output:

<p>To find data enter a search term into the search dialog, or click on the column head...

<p>You can perform numerous functions by clicking the <strong>Actions</strong> button....

<p>If you want to save your customizations select report, or click download to unload ...

<p>Click the <strong>Reset</strong> button to reset the interactive report back to the...
<p>...

STOP_APEX_ENGINE

Ends the request immediately, after your code has written a complete response such as a redirect or a file download, so APEX adds nothing more. It works by raising apex_application.e_stop_apex_engine, so a when others handler in your code must raise it again.

Syntax:

apex_application.stop_apex_engine

This example needs a session of application 200, page 1. It catches the exception on purpose to show it; real code lets it propagate.

Example:

begin
    begin
        -- in a process: owa_util.redirect_url('https://vinish.dev');
        apex_application.stop_apex_engine;
        dbms_output.put_line('not reached');
    exception
        when apex_application.e_stop_apex_engine then
            dbms_output.put_line('e_stop_apex_engine raised: ' || sqlerrm);
            -- in real code: raise;  (let APEX stop the request)
    end;
end;
/

Output:

e_stop_apex_engine raised: ORA-20876: Stop APEX Engine

The classic bug is a when others handler that logs and swallows this exception, after which APEX carries on rendering the page on top of your download. If you catch everything, add a handler for e_stop_apex_engine that simply raises.

Pages: APEX_PAGE

GET_URL

Returns the URL of a page, with the request, cache clearing, item values, and, for pages with page access protection, the checksum. For a modal dialog page it returns the #action$a-dialog-open link that opens the dialog. It is the safe PL/SQL counterpart of building URLs in JavaScript.

Syntax:

apex_page.get_url(
    p_application        in varchar2 default null,
    p_page               in varchar2 default null,
    p_session            in number   default apex_application.g_instance,
    p_request            in varchar2 default null,
    p_debug              in varchar2 default null,
    p_clear_cache        in varchar2 default null,
    p_items              in varchar2 default null,
    p_values             in varchar2 default null,
    p_printer_friendly   in varchar2 default null,
    p_trace              in varchar2 default null,
    p_x01                in varchar2 default null,
    p_hash               in varchar2 default null,
    p_triggering_element in varchar2 default 'this',
    p_plain_url          in boolean  default false,
    p_absolute_url       in boolean  default false) return varchar2

This example needs a session of application 200, page 1. It masks the session ID in the output.

Example:

declare
    procedure show(p_label varchar2, p_url varchar2) is
    begin
        dbms_output.put_line(rpad(p_label, 10) || regexp_replace(substr(p_url, 1, 80), '\d{10,}', '<session>')
                             || case when length(p_url) > 80 then '...' end);
    end;
begin
    show('page:', apex_page.get_url(p_page => 'customers'));
    show('request:', apex_page.get_url(p_page => 'products', p_request => 'EXPORT', p_plain_url => true));
    show('absolute:', apex_page.get_url(p_page => 'home', p_absolute_url => true, p_session => 0));
    show('dialog:', apex_page.get_url(p_page => 3, p_clear_cache => '3',
                                      p_items => 'P3_CUSTOMER_ID', p_values => '42'));
end;
/

Output:

page:     /r/apexbook/api-lab/customers?session=<session>
request:  /r/apexbook/api-lab/products?request=EXPORT&session=<session>&cs=3KSAcpPwqz...
absolute: /r/apexbook/api-lab/home?session=0
dialog:   #action$a-dialog-open?url=%2Fr%2Fapexbook%2Fapi-lab%2Fcustomer%3Fp3_customer_id%...

The products URL carries a cs checksum, which APEX adds for pages with page access protection, and the last URL is a dialog link because page 3 is a modal dialog. The absolute URL has no host because the example ran outside a web request. Using GET_URL instead of concatenating f?p strings is what makes links to protected and dialog pages work; the JavaScript side of opening dialogs is covered in the guide to submitting pages and opening dialogs with apex.page and apex.navigation.

GET_PAGE_MODE and IS_READ_ONLY

GET_PAGE_MODE returns a page's mode: NORMAL, or MODAL for a modal dialog page. IS_READ_ONLY tells, during rendering, whether the current page is read-only through its Read Only condition.

Syntax:

apex_page.get_page_mode(p_application_id in number, p_page_id in number) return varchar2
apex_page.is_read_only return boolean

GET_CACHE_DATE and PURGE_CACHE

For pages with Page Caching: the time the page was cached, or null when it is not, and purging the cache, either for one user or for the current session only.

Syntax:

apex_page.get_cache_date(p_application_id in number, p_page_id in number) return date
apex_page.purge_cache(p_application_id in number default apex.g_flow_id, p_page_id in number default apex.g_flow_step_id,
                      p_user_name in varchar2 default null, p_current_session_only in boolean default false)

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

Example:

begin
    dbms_output.put_line('page 2: ' || apex_page.get_page_mode(p_application_id => 200, p_page_id => 2));
    dbms_output.put_line('page 3: ' || apex_page.get_page_mode(p_application_id => 200, p_page_id => 3));
    dbms_output.put_line('read-only: ' || case when apex_page.is_read_only then 'yes' else 'no' end);
    dbms_output.put_line('cache date: ' || nvl(to_char(apex_page.get_cache_date(200, 1)), '(not cached)'));
    apex_page.purge_cache(p_application_id => 200, p_page_id => 1);
    dbms_output.put_line('purged');
end;
/

Output:

page 2: NORMAL
page 3: MODAL
read-only: no
cache date: (not cached)
purged

GET_UI_TYPE and IS_DESKTOP_UI are deprecated.

Regions: APEX_REGION

GET_ID

Returns a region's internal ID from its static ID, which is the ID the package's other subprograms need. A second overload takes the region name instead.

Syntax:

apex_region.get_id(p_application_id in number default apex.g_flow_id, p_page_id in number,
                   p_dom_static_id in varchar2) return number

This example needs a session of application 200, page 2. It checks the result against the APEX_APPLICATION_PAGE_REGIONS dictionary view.

Example:

declare
    l_region_id number;
    l_view_id   number;
begin
    l_region_id := apex_region.get_id(p_page_id => 2, p_dom_static_id => 'customers');
    select region_id into l_view_id
      from apex_application_page_regions
     where application_id = 200 and page_id = 2 and static_id = 'customers';
    dbms_output.put_line('get_id returns the region ID: ' || case when l_region_id = l_view_id then 'yes' end);
end;
/

Output:

get_id returns the region ID: yes

Looking regions up by static ID keeps your code working after an export and import, when internal IDs change.

OPEN_QUERY_CONTEXT

Opens an APEX_EXEC query context on a region's data: its source, with the filters, sort, and saved report settings of the current session, exactly as the user sees it. You then process the rows in PL/SQL. It runs in an autonomous transaction.

Syntax:

apex_region.open_query_context(
    p_page_id               in number,
    p_region_id             in number,
    p_component_id          in number   default null,
    p_view_mode             in varchar2 default null,
    p_additional_filters    in apex_exec.t_filters default apex_exec.c_empty_filters,
    p_outer_sql             in varchar2 default null,
    p_first_row             in number   default null,
    p_max_rows              in number   default null,
    p_total_row_count       in boolean  default false,
    p_total_row_count_limit in number   default null,
    p_parent_column_values  in apex_exec.t_parameters default apex_exec.c_empty_parameters) return apex_exec.t_context

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

Example:

declare
    l_context apex_exec.t_context;
begin
    l_context := apex_region.open_query_context(
        p_page_id   => 2,
        p_region_id => apex_region.get_id(p_page_id => 2, p_dom_static_id => 'customers'),
        p_max_rows  => 3,
        p_total_row_count => true);
    dbms_output.put_line('rows in the report: ' || apex_exec.get_total_row_count(l_context));
    while apex_exec.next_row(l_context) loop
        dbms_output.put_line(apex_exec.get_varchar2(l_context, 'FIRST_NAME') || ' '
            || apex_exec.get_varchar2(l_context, 'LAST_NAME') || ' - '
            || apex_exec.get_varchar2(l_context, 'CITY'));
    end loop;
    apex_exec.close(l_context);
end;
/

Output:

rows in the report: 250
Wei Chen - Asheville
Kofi White - Asheville
Amanda MacLeod - Salt Lake City

This is the right tool whenever a process must act on "the rows the user is looking at", for example emailing a filtered report, because it applies the user's own filters and sort without you rebuilding the query. Always close the context when you are done.

EXPORT_DATA

Exports a region's data as the user sees it, in an APEX_DATA_EXPORT format, CSV, HTML, XLSX, PDF, JSON, or XML, and returns the file as a BLOB or, with p_as_clob, a CLOB. The other parameters are those of OPEN_QUERY_CONTEXT and of APEX_DATA_EXPORT.EXPORT, such as page size, orientation, and file name.

Syntax:

apex_region.export_data(p_format in apex_data_export.t_format, p_page_id in number, p_region_id in number,
                        ... ) return apex_data_export.t_export

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

Example:

declare
    l_export apex_data_export.t_export;
begin
    l_export := apex_region.export_data(
        p_format    => apex_data_export.c_format_csv,
        p_page_id   => 2,
        p_region_id => apex_region.get_id(p_page_id => 2, p_dom_static_id => 'customers'),
        p_max_rows  => 3,
        p_as_clob   => true);
    dbms_output.put_line(l_export.file_name || ' (' || l_export.mime_type || ')');
    for r in (select column_value as line from apex_string.split(substr(l_export.content_clob, 1, 1000), chr(10)) where rownum <= 3) loop
        dbms_output.put_line(substr(r.line, 1, 90) || case when length(r.line) > 90 then '...' end);
    end loop;
end;
/

Output:

Customers.csv (text/csv)
Customer Type,First Name,Last Name,Company Name,Email,Phone,Address Line1,City,State Provi...
RETAIL,Wei,Chen,,wei.chen@example.com,(457) 237-9028,2192 Main St,Asheville,North Carolina...
RETAIL,Kofi,White,,kofi.white@example.com,(424) 795-1887,6741 Pine St,Asheville,North Caro...

The file is named after the region, and the header row uses the column headings. For other ways to produce files, see the guide to files, PDF export, and printing in Oracle APEX.

RESET, CLEAR, IS_READ_ONLY, GET_CACHE_DATE, and PURGE_CACHE

RESET returns a region to its defined settings: pagination, sorting, and the report settings of interactive reports and grids. CLEAR clears the session's pagination and interactive report settings. IS_READ_ONLY tells, during rendering, whether the current region is read-only, and returns null outside a region. GET_CACHE_DATE and PURGE_CACHE work for regions with caching as they do for pages.

Syntax:

apex_region.reset(p_application_id in number default apex_application.g_flow_id, p_page_id in number,
                  p_region_id in number, p_component_id in number default null)
apex_region.clear(...same parameters...)
apex_region.is_read_only return boolean
apex_region.get_cache_date(p_application_id in number, p_page_id in number, p_static_id in varchar2) return date
apex_region.purge_cache(p_application_id in number default apex.g_flow_id, p_page_id in number default null,
                        p_region_id in number default null, p_current_session_only in boolean default false)

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

Example:

declare
    l_region_id number := apex_region.get_id(p_page_id => 2, p_dom_static_id => 'customers');
begin
    apex_region.reset(p_page_id => 2, p_region_id => l_region_id);   -- report settings back to the default
    apex_region.clear(p_page_id => 2, p_region_id => l_region_id);   -- pagination and IR settings of the session
    dbms_output.put_line('read-only: ' || nvl(case apex_region.is_read_only when true then 'yes' when false then 'no' end, '(no region)'));
    dbms_output.put_line('cache date: ' || nvl(to_char(apex_region.get_cache_date(200, 2, 'customers')), '(not cached)'));
    apex_region.purge_cache(p_application_id => 200, p_page_id => 2, p_region_id => l_region_id);
    dbms_output.put_line('done');
end;
/

Output:

read-only: no
cache date: (not cached)
done

A Reset button on a report page can be as simple as a process that calls RESET. Interactive reports themselves are covered in the complete interactive report guide.

Application Settings: APEX_APP_SETTING

GET_VALUE and SET_VALUE

Read and change an application setting, defined under Shared Components, Application Settings. Settings hold configuration that administrators can change without editing the application, such as a support email address. A setting subscribed from another application cannot be changed. By default, an unknown setting returns null; with p_raise_error, it raises an error.

Syntax:

apex_app_setting.get_value(p_name in varchar2, p_raise_error in boolean default false) return varchar2
apex_app_setting.set_value(p_name in varchar2, p_value in varchar2, p_raise_error in boolean default false)

This example needs a session of application 200, page 1. Its last call fails on purpose, to show p_raise_error.

Example:

declare
    l_email varchar2(200);
begin
    l_email := apex_app_setting.get_value(p_name => 'SUPPORT_EMAIL');
    dbms_output.put_line('SUPPORT_EMAIL: ' || l_email);
    apex_app_setting.set_value(p_name => 'SUPPORT_EMAIL', p_value => 'help@orbit-outfitters.example');
    dbms_output.put_line('changed:       ' || apex_app_setting.get_value('SUPPORT_EMAIL'));
    apex_app_setting.set_value('SUPPORT_EMAIL', l_email);                     -- put it back
    dbms_output.put_line('unknown:       ' || nvl(apex_app_setting.get_value('NO_SUCH_SETTING'), '(null)'));
    begin
        dbms_output.put_line(apex_app_setting.get_value('NO_SUCH_SETTING', p_raise_error => true));
    exception
        when others then dbms_output.put_line('p_raise_error: ' || substr(sqlerrm, 1, 80));
    end;
end;
/

Output:

SUPPORT_EMAIL: sales-support@orbit-outfitters.example
changed:       help@orbit-outfitters.example
unknown:       (null)
p_raise_error: ORA-20987: APEX - Requested Application Setting #NO_SUCH_SETTING# is not defined

Pass p_raise_error for settings your code cannot work without, so a missing setting fails loudly instead of quietly returning null. How settings fit into an application's logic is covered in the guide to items, processes, settings, and build options.

Theme Styles: APEX_THEME

SET_USER_STYLE, GET_USER_STYLE, CLEAR_USER_STYLE, and CLEAR_ALL_USERS_STYLE

A user's theme style preference, the style chosen with Customize in the user menu, overrides the application's style for that user. These procedures set, read, and clear it for one user or for all users.

Syntax:

apex_theme.set_user_style(p_application_id in number default <current>, p_user in varchar2 default <current>,
                          p_theme_number in number default <current>, p_id in number)
apex_theme.get_user_style(p_application_id, p_user, p_theme_number) return number
apex_theme.clear_user_style(p_application_id, p_user, p_theme_number)
apex_theme.clear_all_users_style(p_application_id, p_theme_number)

This example needs a session of application 200, page 1. It looks up the style's ID by its static ID in the APEX_APPLICATION_THEME_STYLES view.

Example:

declare
    l_style_id number;
begin
    select theme_style_id into l_style_id
      from apex_application_theme_styles
     where application_id = 200 and static_id = 'vita-dark';
    dbms_output.put_line('user style: ' || nvl(to_char(apex_theme.get_user_style(p_application_id => 200, p_user => 'ADMIN', p_theme_number => 42)), '(none)'));
    apex_theme.set_user_style(p_application_id => 200, p_user => 'ADMIN', p_theme_number => 42, p_id => l_style_id);
    dbms_output.put_line('user style: ' || case when apex_theme.get_user_style(200, 'ADMIN', 42) = l_style_id then 'Vita - Dark' end);
    apex_theme.clear_user_style(p_application_id => 200, p_user => 'ADMIN', p_theme_number => 42);
    dbms_output.put_line('after clear_user_style: ' || nvl(to_char(apex_theme.get_user_style(200, 'ADMIN', 42)), '(none)'));
    apex_theme.clear_all_users_style(p_application_id => 200, p_theme_number => 42);
end;
/

Output:

user style: (none)
user style: Vita - Dark
after clear_user_style: (none)

Letting users pick their own style from the application is covered in letting users select the theme of their choice.

SET_SESSION_STYLE and SET_SESSION_STYLE_CSS

Set the style of the current session, either by a style's static ID or as CSS file URLs and page classes without a style definition. They are typically called after login, from a user's profile. A user's own style preference still takes precedence.

Syntax:

apex_theme.set_session_style(p_application_id in number, p_theme_number in number, p_name in varchar2)
apex_theme.set_session_style_css(p_application_id in number default <current>, p_theme_number in number default <current>,
                                 p_css_file_urls in varchar2, p_page_css_classes in varchar2 default null)

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

Example:

begin
    apex_theme.set_session_style(p_application_id => 200, p_theme_number => 42, p_name => 'redwood-light');
    apex_theme.set_session_style_css(p_theme_number => 42,
        p_css_file_urls => '#APP_FILES#orbit-print.css', p_page_css_classes => 'orbit-print');
    dbms_output.put_line('session style set: the next pages of this session use Redwood Light');
end;
/

Output:

session style set: the next pages of this session use Redwood Light

SET_CURRENT_STYLE, ENABLE_USER_STYLE, and DISABLE_USER_STYLE

SET_CURRENT_STYLE changes the application's style for everyone, which is a change to the application definition. ENABLE_USER_STYLE and DISABLE_USER_STYLE turn the users' Customize option on and off.

Syntax:

apex_theme.set_current_style(p_application_id in number default <current>, p_theme_number in number, p_id in varchar2)
apex_theme.enable_user_style(p_application_id in number default <current>, p_theme_number in number default <current>)
apex_theme.disable_user_style(p_application_id in number default <current>, p_theme_number in number default <current>)

This example needs a session of application 200, page 1. It switches the style and then switches it back.

Example:

declare
    function style_id(p_static_id varchar2) return number is
        l_id number;
    begin
        select theme_style_id into l_id from apex_application_theme_styles
         where application_id = 200 and static_id = p_static_id;
        return l_id;
    end;
    procedure show is
        l_current varchar2(100);
    begin
        select name into l_current from apex_application_theme_styles
         where application_id = 200 and is_current = 'Yes';
        dbms_output.put_line('current style: ' || l_current);
    end;
begin
    show;
    apex_theme.set_current_style(p_application_id => 200, p_theme_number => 42, p_id => style_id('vita-slate'));
    apex_theme.disable_user_style(p_application_id => 200, p_theme_number => 42);
    show;
    apex_theme.set_current_style(p_application_id => 200, p_theme_number => 42, p_id => style_id('iris'));   -- back
    apex_theme.enable_user_style(p_application_id => 200, p_theme_number => 42);
    show;
end;
/

Output:

current style: Iris
current style: Vita - Slate
current style: Iris

Because SET_CURRENT_STYLE changes the application definition, treat it like any other change to a production application. Theme styles themselves are covered in the guide to themes, templates, and Theme Roller.

Conclusion

APEX_APPLICATION exposes the request's global variables, writes page help, and ends a request cleanly with STOP_APEX_ENGINE, which your exception handlers must let through. APEX_PAGE.GET_URL builds URLs with checksums and dialog links, and its siblings report page modes and manage page caching. APEX_REGION finds regions by static ID, reads and exports their data exactly as the user sees it, and resets them. APEX_APP_SETTING reads and changes the settings administrators maintain, and APEX_THEME manages theme styles for one user, one session, or the whole application.