How to Create APEX Sessions and Manage Session State from PL/SQL

Inside an Oracle APEX application, PL/SQL always runs in a session: a process, computation, or validation can read APP_USER and page items without thinking about it. Outside a request, in a scheduler job, a test script, or a SQL Developer worksheet, there is no session, and many APEX APIs fail or return nothing. APEX_SESSION fixes that by creating, attaching to, and deleting sessions from PL/SQL. APEX_SESSION_STATE then reads and writes item values with proper data types, where v and apex_util.set_session_state only know strings.

This guide covers both packages with tested examples and their real output, including two format-mask traps in APEX 26.1 that make GET_NUMBER and GET_TIMESTAMP fail, and a 32,767-character limit the documentation does not mention.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200, whose page 20 holds sample items such as P20_TEXT, P20_NUMBER, P20_DATE, and P20_SWITCH. Run them as the application's parsing schema in SQL Developer, SQLcl, or SQL*Plus with server output switched on. Do not use SQL Workshop's SQL Commands or SQL Scripts: APEX does not support creating sessions there.

Replace the application ID, page, user, and item names with your own. Examples that say they need a session expect one to exist already; create it first with APEX_SESSION.CREATE_SESSION, exactly as the first example does, and delete it when you are done. The output under each example is exactly what the database printed.

Creating and Using Sessions: APEX_SESSION

CREATE_SESSION and DELETE_SESSION

CREATE_SESSION creates a session of an application for a user and sets up the environment as a request would, including APP_ID, APP_USER, and page items; it also runs the application's Initialization PL/SQL Code. DELETE_SESSION deletes a session, the current one by default, after running the Cleanup PL/SQL Code. Use them in scripts, jobs, and tests that call APEX APIs outside a web request.

Syntax:

apex_session.create_session(
    p_app_id                   in number,
    p_page_id                  in number,
    p_username                 in varchar2,
    p_call_post_authentication in boolean default false)

apex_session.delete_session(
    p_session_id in number default apex_application.g_instance)

Example:

begin
    apex_session.create_session(
        p_app_id   => 200,
        p_page_id  => 1,
        p_username => 'ADMIN');
    dbms_output.put_line('app ' || v('APP_ID') || ', page ' || v('APP_PAGE_ID')
        || ', user ' || v('APP_USER') || ', session ' || v('APP_SESSION'));
    -- the application's Initialization PL/SQL Code and computations ran:
    dbms_output.put_line('pending approvals: ' || v('G_PENDING_APPROVALS'));
    apex_session.delete_session;
    dbms_output.put_line('after delete_session: session ' || nvl(v('APP_SESSION'), '(none)'));
end;
/

Output:

app 200, page 1, user ADMIN, session 11537280069438
pending approvals:
after delete_session: session (none)

The pending approvals item is empty because the application process that fills it runs Before Header when page 1 is requested, not when a session is created. Code that relies on page-rendering processes will not see their results in a session made this way. Note also that p_username is taken on trust: the session belongs to that user without any password check, so keep scripts that create sessions away from anything a user can influence.

ATTACH and DETACH

ATTACH joins an existing session, for example to continue its work in a later database call such as a job the session started. DETACH leaves the session, running the cleanup code but keeping the session and its state.

Syntax:

apex_session.attach(p_app_id in number, p_page_id in number, p_session_id in number)
apex_session.detach

Example:

declare
    l_session_id number;
begin
    apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'ADMIN');
    l_session_id := v('APP_SESSION');
    apex_util.set_session_state('P20_TEXT', 'kept in the session');
    apex_session.detach;                                  -- the session still exists
    dbms_output.put_line('detached: session ' || nvl(v('APP_SESSION'), '(none)'));

    apex_session.attach(p_app_id => 200, p_page_id => 20, p_session_id => l_session_id);
    dbms_output.put_line('attached again: P20_TEXT = ' || v('P20_TEXT'));
    apex_session.delete_session(p_session_id => l_session_id);
end;
/

Output:

detached: session (none)
attached again: P20_TEXT = kept in the session

The value set before DETACH was still there after ATTACH, because detaching leaves the session in place. A common pattern is for a page process to pass its session ID to a background job, which attaches to it to read the user's items and write results back.

SET_DEBUG, SET_TRACE, and SET_TENANT_ID

SET_DEBUG turns debug logging on for a session's future requests, at a level from 1 to 9, or off with null. SET_TRACE turns on SQL trace with 'SQL', or off with null. Both need a commit to take effect. SET_TENANT_ID sets the session's tenant, which APP_TENANT_ID then returns, for multitenant applications; it can be set only once per session.

Syntax:

apex_session.set_debug(p_session_id in number default apex_application.g_instance, p_level in apex_debug.t_log_level)
apex_session.set_trace(p_session_id in number default apex_application.g_instance, p_mode in varchar2)
apex_session.set_tenant_id(p_tenant_id in varchar2)

This example needs a session of application 200, page 1, for the user ADMIN. The second SET_TENANT_ID call fails on purpose, to show the once-per-session rule.

Example:

begin
    apex_session.set_debug(p_level => apex_debug.c_log_level_info);   -- this session's next requests
    apex_session.set_trace(p_mode => 'SQL');
    apex_session.set_tenant_id(p_tenant_id => 'ORBIT-EU');
    dbms_output.put_line('APP_TENANT_ID: ' || v('APP_TENANT_ID'));
    begin
        apex_session.set_tenant_id(p_tenant_id => 'ORBIT-US');
    exception
        when others then dbms_output.put_line('second set_tenant_id: ' || substr(sqlerrm, 1, 72));
    end;
    commit;                                               -- the settings are stored at commit
end;
/

Output:

APP_TENANT_ID: ORBIT-EU
second set_tenant_id: ORA-20987: APEX - The tenant id already exists for the current session.

SET_DEBUG is handy for switching on debugging for one user's session from the back end, when a problem only shows up for them. Reading the resulting log is covered in the guide to debugging, source control, and going to production in Oracle APEX.

Typed Session State: APEX_SESSION_STATE

APEX_SESSION_STATE reads and writes the values of page and application items with a data type. The older v function and apex_util.get_session_state and set_session_state only handle strings. For the basics of session state and how items get their values, see the guide to session state and substitution strings in Oracle APEX.

SET_VALUE

Sets an item's value from a VARCHAR2, NUMBER, BOOLEAN, or CLOB, through four overloads, and commits by default.

Syntax:

apex_session_state.set_value(
    p_item   in varchar2,
    p_value  in varchar2 | number | boolean | clob,
    p_commit in boolean default true)

GET_VARCHAR2, GET_NUMBER, GET_TIMESTAMP, GET_BOOLEAN, and GET_CLOB

Return an item's value as a given type. GET_VARCHAR2 is the same as v. GET_NUMBER and GET_TIMESTAMP convert using the item's format mask, or the session's number and date formats when the item has none.

Syntax:

apex_session_state.get_varchar2(p_item in varchar2) return varchar2
apex_session_state.get_number(p_item in varchar2) return number
apex_session_state.get_timestamp(p_item in varchar2) return timestamp
apex_session_state.get_boolean(p_item in varchar2) return boolean
apex_session_state.get_clob(p_item in varchar2) return clob

This example needs a session of application 200, page 20. Its last call fails on purpose, and the exception handler prints the error.

Example:

begin
    apex_session_state.set_value('P20_TEXT', 'Trailblazer 2-Person Tent');
    apex_session_state.set_value('P20_NUMBER', '1,249.50');          -- in the item's format mask
    apex_session_state.set_value('P20_DATE', '23-SEP-2026');         -- in the session's date format
    apex_session_state.set_value('P20_SWITCH', true);                -- BOOLEAN
    dbms_output.put_line('get_varchar2(P20_TEXT):  ' || apex_session_state.get_varchar2('P20_TEXT'));
    dbms_output.put_line('get_number(P20_NUMBER):  ' || apex_session_state.get_number('P20_NUMBER'));
    dbms_output.put_line('get_timestamp(P20_DATE): ' || to_char(apex_session_state.get_timestamp('P20_DATE'), 'YYYY-MM-DD HH24:MI'));
    dbms_output.put_line('get_boolean(P20_SWITCH): ' || case apex_session_state.get_boolean('P20_SWITCH') when true then 'TRUE' else 'FALSE' end);
    apex_session_state.set_value('P20_NUMBER', 1249.5);              -- a NUMBER is stored as is...
    dbms_output.put_line('v(P20_NUMBER):           ' || v('P20_NUMBER'));
    dbms_output.put_line('get_number(P20_NUMBER):  ' || apex_session_state.get_number('P20_NUMBER'));   -- ...and fails
exception
    when value_error then dbms_output.put_line('get_number: ' || sqlerrm);
end;
/

Output:

get_varchar2(P20_TEXT):  Trailblazer 2-Person Tent
get_number(P20_NUMBER):  1249.5
get_timestamp(P20_DATE): 2026-09-23 00:00
get_boolean(P20_SWITCH): TRUE
v(P20_NUMBER):           1249.5
get_number: ORA-06502: PL/SQL: value or conversion error

The failure is the first trap. SET_VALUE with a NUMBER stores it without the item's format mask, as 1249.5, and GET_NUMBER then cannot read it back through the mask 999G999G990D00. Set numbers as strings in the item's own format, as 1,249.50 here, and GET_NUMBER works. The second trap is similar: SET_VALUE with a TIMESTAMP stores it in the session's timestamp format, which GET_TIMESTAMP does not read for an item without a format mask. Give date items a format mask and set them as strings in that mask.

GET_VALUE

Returns the value as a record of type t_value, with the fields data_type, varchar2_value, clob_value, and boolean_value, of which one is filled.

Syntax:

apex_session_state.get_value(p_item in varchar2) return apex_session_state.t_value

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

Example:

declare
    l_value apex_session_state.t_value;
begin
    apex_session_state.set_value('P20_TEXTAREA', to_clob(rpad('x', 32767, 'x')) || rpad('y', 8000, 'y'), p_commit => false);
    dbms_output.put_line('get_clob:     ' || length(apex_session_state.get_clob('P20_TEXTAREA')) || ' characters');
    dbms_output.put_line('get_varchar2: ' || length(apex_session_state.get_varchar2('P20_TEXTAREA')) || ' characters');
    l_value := apex_session_state.get_value('P20_TEXTAREA');
    dbms_output.put_line('get_value:    varchar2_value ' || length(l_value.varchar2_value)
        || ' characters, clob_value ' || nvl(to_char(length(l_value.clob_value)), '(null)'));
    apex_session_state.set_value('P20_SWITCH', true);
    l_value := apex_session_state.get_value('P20_SWITCH');
    dbms_output.put_line('get_value(P20_SWITCH): boolean_value '
        || case l_value.boolean_value when true then 'TRUE' when false then 'FALSE' else '(null)' end);
end;
/

Output:

get_clob:     32767 characters
get_varchar2: 32767 characters
get_value:    varchar2_value 32767 characters, clob_value (null)
get_value(P20_SWITCH): boolean_value TRUE

The value set was 40,767 characters long, but every read returned only 32,767. In 26.1, a value longer than 32,767 characters is kept as a VARCHAR2 of that length, so GET_CLOB, GET_VARCHAR2, and GET_VALUE all return its first 32,767 characters, and clob_value stays empty. The documentation says GET_VARCHAR2 raises an exception for a CLOB value; it does not, it silently truncates. Keep long values in an APEX collection instead of an item.

For setting items from PL/SQL inside an application, the simpler route is often a computation or a process, as shown in setting a page item value using PL/SQL.

Conclusion

APEX_SESSION gives code outside a request the context APEX APIs need. CREATE_SESSION sets up a session for a user, running the application's initialization code but not page processes; DELETE_SESSION removes it; ATTACH and DETACH let a later call, such as a background job, continue an existing session; and SET_DEBUG, SET_TRACE, and SET_TENANT_ID configure it. APEX_SESSION_STATE reads and writes item values with real data types. Set numbers and dates as strings in the item's format mask, or GET_NUMBER and GET_TIMESTAMP fail, and keep values longer than 32,767 characters in a collection, because longer item values are silently cut.