Previous Next JavaScript must be enabled to correctly display this content

## 3.9 Managing Session State Values

Manage session state to store and retrieve values for a user as the user navigates between different application pages.

When building interactive, data-driven web applications, the ability to access and manage session state values is critical. In Oracle APEX, session state is automatically managed for every page and easily referenced in static HTML or logic controls such as processes or validations.

- About Referencing Session State
  Reference item values stored in session state in regions, computations, processes, validations, and branches. An item can be a field, a text area, a password, a select list, or a checkbox.
- About Setting Session State
  Set session state using form submissions, bind variables, computations, or f?p syntax. Session state can also be set using the `APEX_SESSION_STATE.SET` APIs.
- Clearing Session State
  Clearing a cached value resets the value to null. You can clear the cached value for specific items, all items on a page, all pages in an application, or the current user session.
- Referencing Session State Using Bind Variable Syntax
  Use bind variable syntax anywhere where you are using SQL or PL/SQL to reference session state of a specified item.
- About Session Cloning
  Use session cloning to open a new browser window in an Oracle APEX application and have it generate a new distinct session identifier and copy the session values from the original APEX session to the new one.

See Also:

Understanding Session State Management and Referencing Item Values

Parent topic: App Builder Concepts

### 3.9.1 About Referencing Session State

Reference item values stored in session state in regions, computations, processes, validations, and branches. An item can be a field, a text area, a password, a select list, or a checkbox.

The following table describes the supported syntax for referencing item values.

Type | Syntax | Description
--- | --- | ---
SQL | Standard item syntax: `:MY_ITEM` Syntax for items containing special characters: `:"MY_ITEM"` | For items whose names are no longer than 30 characters, precede the item name with a colon (:). Use this syntax for references within a SQL query and within PL/SQL. To reference page items containing special, multibyte, or unicode characters, wrap the page item name in double quotation marks. The data type of the bind variable is the Session State Data Type of the item. Most items are `VARCHAR2` by default, but certain item types can be set to `CLOB`.
PL/SQL | `V('MY_ITEM')` or `APEX_SESSION_STATE.GET_VARCHAR2('MY_ITEM')` | Use PL/SQL syntax to reference an item value using the `V` function. You can use the shorthand, `V` function, in place of `APEX_SESSION_STATE.GET_VARCHAR2`. Use this syntax when utilizing Oracle APEX variables directly within a database object, such as a function, trigger, or Oracle Data Redaction policy. Tip: When using `V('MY_ITEM')`, `apex_session_state.get_clob('MY_ITEM')` or similar in a SQL query, Oracle recommends using Fast Dual functionality `=> (select v('my_item') from dual)` to improve performance.
PL/SQL | `APEX_SESSION_STATE.GET_CLOB('MY_CLOB_ITEM')` | This function returns the value of a `CLOB` page item. Tip: When using `V('MY_ITEM')`, `apex_session_state.get_clob('MY_ITEM')` or similar in a SQL query, Oracle recommends using Fast Dual functionality `=> (select v('my_item') from dual)` to improve performance.
PL/SQL | `APEX_SESSION_STATE.GET_NUMBER('MY_NUMERIC_ITEM')` | This function returns the value of a page item as a `NUMBER`. The item's Format Mask is used to perform the conversion. Tip: When using `V('MY_ITEM')`, `apex_session_state.get_clob('MY_ITEM')` or similar in a SQL query, Oracle recommends using Fast Dual functionality `=> (select v('my_item') from dual)` to improve performance.
PL/SQL | `APEX_SESSION_STATE.GET_TIMESTAMP('MY_NUMERIC_ITEM')` | This function returns the value of a page item as a `TIMESTAMP`. The item's Format Mask is used to perform the conversion. Tip: When using `V('MY_ITEM')`, `apex_session_state.get_clob('MY_ITEM')` or similar in a SQL query, Oracle recommends using Fast Dual functionality `=> (select v('my_item') from dual)` to improve performance.
Static text (exact) | Standard item syntax: `&MY_ITEM.` Syntax for items containing special characters: `&"MY_ITEM".` | For static text or an exact substitution, use the convention `&ITEM_NAME` followed by a period (.). To reference page items containing special, multibyte, or unicode characters, wrap the page item name in double quotation marks. Page items of Session State Data Type `CLOB` cannot be references via this syntax. A different output mechanism must be used to fetch their values, such as a page item in Read Only mode, a Display Only page item, or its manual output using a Dynamic Content region.

See Also:

Changes in Oracle APEX API Reference in Oracle APEX API Reference

Parent topic: Managing Session State Values

### 3.9.2 About Setting Session State

Set session state using form submissions, bind variables, computations, or f?p syntax. Session state can also be set using the `APEX_SESSION_STATE.SET` APIs.

Methods for Setting Session State

See Also:

Changes in Oracle APEX API Reference in Oracle APEX API Reference

You can set the value of a page item in your application and therefore set session state using the following methods:

- Form submission. See the next section.
- Bind variable. See Referencing Session State Using Bind Variable Syntax.
- Computation. See Understanding Application Computations.
- f?p syntax. See About Using f?p Syntax to Link Pages.

About Setting Session State with a Form Submission

When a user submits a page, the Oracle APEX engine automatically stores values typed into fields (items) in session state. For example, suppose you have an application containing two pages. The first page of the application contains a form in which a user can enter a phone number. You defined this form by creating an item named `P1_PHONENO`. On the second page, you want to display the information the user enters in the form.

When the page is submitted, APEX captures the value entered in the phone number field and stores the value for future use. On the second page, the phone number entered by the user can then be retrieved from session state using the name `P1_PHONE_NO` with the appropriate syntax described in About Referencing Session State.

About Controlling How Items Write Session State

You can control whether a page item writes its session state into persistent (disk) session state or just into memory by configuring the item attribute, Maintain Session State. Available Maintain Session State options include:

- Per Request (Memory Only) - Do not save state in the database. State is only available when processing the current request. When AJAX requests need to use an item, make sure to pass the item name using the Page Items To Submit attribute.
- Per Session (Disk) - Maintain for each session by storing the value in the database, to access it across requests.
- Per User (Disk) - Value is saved in a user attribute repository and it is also available for later APEX sessions.

Note:

When creating database items that work with a Form region (for example as part of a wizard), Per Request (Memory Only) is the default. Per User (Disk) is not available for these items.

See Also:

Controlling How Page Items Write Session State

Parent topic: Managing Session State Values

### 3.9.3 Clearing Session State

Clearing a cached value resets the value to null. You can clear the cached value for specific items, all items on a page, all pages in an application, or the current user session.

- About Clearing Cache for an Item
  View examples for clearing cache for a single item.
- About Clearing Cache for All Page Items
  View examples for clearing cache for all page items.
- About Clearing Report Regions
  Learn about clearing report cache.
- Clearing Cache for an Entire Application
  View an eample of clearing cache for an application.
- About Resetting an Application Completely
  Learn about resetting an application completely.
- About Clearing Cache for the Current User Session
  View an example of clearing application cache for the current user session.

Parent topic: Managing Session State Values

#### 3.9.3.1 About Clearing Cache for an Item

View examples for clearing cache for a single item.

Clearing cache for a single item resets the value of the item to null. For example, you might use this approach to make sure a specific item's value is null when a page is prepared for rendering.

Example 3-4 Example: Clearing Cache for an Item

The following example uses standard `f?p` syntax to clear the cache for an item. This example calls page 5 of application 100. Placing `MY_ITEM` in the `ClearCache` position of the `f?p` syntax resets the value of `MY_ITEM` to `NULL`.

    f?p=100:5:&APP_SESSION.::NO:MY_ITEM

The following example resets the value of the items `THE_EMPNO` and `THE_DEPTNO`:

    f?p=100:5:&APP_SESSION.::NO:THE_EMPNO,THE_DEPTNO

Parent topic: Clearing Session State

#### 3.9.3.2 About Clearing Cache for All Page Items

View examples for clearing cache for all page items.

Caching application items is an effective way to maintain session state. However, there are occasions when you may want to clear the cache for all items on a page. For example, suppose you needed to clear all fields on a page when a user clicks a link that creates a new order. By clearing the cache for an entire page, you set the value of all items on the page to null.

Example 3-5 Example: Clearing Cache for Two Pages and Resetting Pagination

This example clears the session cache for two pages and resets pagination.

    f?p=6000:6003:&APP_SESSION.::NO:RP,6004,6014

This example:

- Runs page 6003 of application 6000 and uses the current session ID.

- Indicates to not show debug information (`NO`).

- Clears all values maintained by the current session's cache for items of pages 6004 and 6014.

- Resets region pagination (`RP`) on page 6003 (the requested page).

Example 3-6 Example: Clearing Cache on a Page and Passing an Item Value

This example shows how to implement an update form. It clears existing information and sets the item's value (typically a primary key).

    f?p=6000:6003:&APP_SESSION.::NO:6003:MY_ITEM:1234

This example:

- Runs page 6003 of application 6000 and uses the current session ID

- Indicates to not show debug information (`NO`)

- Clears all values maintained by the current session's cache for items on page 6003

- Sets the session state of an item called `MY_ITEM` to the value `1234`

Example 3-7 Example: Clearing Session Cache on a Page and Passing Values to Multiple Items

This example demonstrates how to implement an update form. It clears existing information, sets the item's value (typically a primary key), and passes values to multiple items.

    f?p=6000:6004:&APP_SESSION.::NO:6003:MY_ITEM1,MY_ITEM2,MY_ITEM3:1234,,5678

This example:

- Runs page 6004 of application 6000 and uses the current session ID

- Clears the current session's cache for items on page 6003

- Indicates to not show debug information (`NO`)

- Sets the value of `MY_ITEM1` to 1234, sets the value of `MY_ITEM2` to null (indicated by the comma used as placeholder), and sets the value of `MY_ITEM3` to 5678

Parent topic: Clearing Session State

#### 3.9.3.3 About Clearing Report Regions

Learn about clearing report cache.

Report settings can be cached. Depending on the report types, different settings are cached. Use the following to clear report cache settings:

- `RR` - Resets interactive report, interactive grids, or classic report and also resets report pagination. If the target page contains interactive report or interactive grids, the report is returned to the default report settings specified by the developer or saved by the user. If the target page contains classic report, the report sorting resets to developer defined sorting.
- `CR` - This applies only to interactive report. `CR` clears interactive report and resets report pagination. This clears all of the session report settings such as control break, aggregate, flashback, chart, number of rows to display, filter, highlight, computation, group by, and pivot.
- `RP` - Resets interactive report or classic report pagination.

Example 3-8 Resetting Report Settings and Pagination

This example resets report settings and pagination of the target page report regions:

    f?p=6000:6003:&APP_SESSION.::NO:RR

Note:

Starting with Oracle APEX release 2.1, you do not need to define separate clear cache syntax to reset report and reset pagination. `RR` will handle both.

Parent topic: Clearing Session State

#### 3.9.3.4 Clearing Cache for an Entire Application

View an eample of clearing cache for an application.

This example clears the application's cache by using `f?p` syntax and creating a `Clear Cache` argument using the keyword APP.

    f?p=App:Page:Session::NO:APP

Parent topic: Clearing Session State

#### 3.9.3.5 About Resetting an Application Completely

Learn about resetting an application completely.

Resetting the cache for an entire application does not restore the application to a completely reset state. For example, if an application includes on-new instance computations or on-new instance processes, the Oracle APEX engine runs these computations and processes when the application session is created. Then, it processes the clear cache request and displays the requested page.

To reset an application completely without a session ID (if no cookie is used to track the session ID), you must request it using a URL without a session ID, or by calling `APEX_UTIL`.`CLEAR_APP_CACHE` from another application. If the session ID is tracked using a cookie, you must logout to reset the state.

Parent topic: Clearing Session State

#### 3.9.3.6 About Clearing Cache for the Current User Session

View an example of clearing application cache for the current user session.

Another approach to clearing an application's cache is to create a `Clear Cache` argument using the keyword `SESSION`. For example:

    f?p=6000:6004:12507785108488427528::NO:SESSION

Parent topic: Clearing Session State

### 3.9.4 Referencing Session State Using Bind Variable Syntax

Use bind variable syntax anywhere where you are using SQL or PL/SQL to reference session state of a specified item.

- About Using Bind Variable Syntax
  Learn about bind variable syntax.
- About Using Bind Variables in Regions Based on a SQL Query or LOV
  Learn about using bind variables in regions defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV).
- About Using Bind Variables in Regions Based on PL/SQL
  Learn about using bind variables in regions based on PL/SQL.

Parent topic: Managing Session State Values

#### 3.9.4.1 About Using Bind Variable Syntax

Learn about bind variable syntax.

In the following example, the search string is a page item.

    SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%'

If the region type is defined as SQL Query, you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.

When using bind variable syntax, remember the following rules:

- Bind variable names must correspond to an item name.

- Bind variable names are not case-sensitive.

- Bind variable names cannot be longer than 30 characters (that is, they must be a valid Oracle identifier).

  Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variable syntax, the item name must be 30 characters or less.

- The data type of bind variables is always `VARCHAR2`. When a bind variable semantically contains a datetime or numeric value, you must perform an explicit conversion to the appropriate data type. For example:

      SELECT * FROM employees WHERE start_date < to_date(:DATE_STRING, 'DD-MON-YYYY')

Parent topic: Referencing Session State Using Bind Variable Syntax

#### 3.9.4.2 About Using Bind Variables in Regions Based on a SQL Query or LOV

Learn about using bind variables in regions defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV).

If your region type is defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV), you can reference session state using the following syntax:

    :MY_ITEM

One common way to do this is to incorporate a session state variable in a `WHERE` clause. The following example shows how to bind the value of the item `THE_DEPTNO` into a region defined from a SQL Query.

    SELECT last_name, job_id, salary
    FROM employees
    WHERE department_id = :THE_DEPTNO

See Also:

About Regions for information about creating regions

Parent topic: Referencing Session State Using Bind Variable Syntax

#### 3.9.4.3 About Using Bind Variables in Regions Based on PL/SQL

Learn about using bind variables in regions based on PL/SQL.

For region types, processes, computations, validations, conditions, and so on that are defined as PL/SQL dynamic content, regions are constructed using PL/SQL anonymous block syntax. In other words, the beginning and ending keywords are used to enclose the PL/SQL block. For example:

    IF :P1_JOB IS NOT NULL THEN
      INSERT INTO employees (employee_id, first_name, job_id)
      VALUES (:P1_EMP_ID, :P1_NAME, :P1_JOB)
    end if;

In this example, the values of the `employee_id`, `first_name`, and `job_id` are populated by the values of `P1_EMP_ID`, `P1_NAME`, and `P1_JOB`.

Parent topic: Referencing Session State Using Bind Variable Syntax

### 3.9.5 About Session Cloning

Use session cloning to open a new browser window in an Oracle APEX application and have it generate a new distinct session identifier and copy the session values from the original APEX session to the new one.

In previous releases, opening multiple windows (or browser tabs) in the same APEX application resulted in a number of issues. Typically, all the browser tabs shared the same session and session state which resulted in unpredictable and undesirable results.

To use session cloning, the developer must provide a method for end user to open a new browser tab and specify the `REQUEST` value of `APEX_CLONE_SESSION`. The following is an example URL:

`f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APEX_CLONE_SESSION`

Best Practices When Using Session Cloning

When using this session cloning, developers should remember:

- The idle time associated with the Oracle APEX session would be affected by any of the APEX sessions, original or cloned ones.
- When a user logs out of one session (original or cloned), all other associated sessions will be logged out.
- The maximum session duration would be a function of the original APEX session and when it was created.
- An administrative user can enable or disable this feature using the procedure:

      apex_instance_admin.set_parameter(
          p_parameter => 'CLONE_SESSION_ENABLED',
          p_value     => 'Y');

See Also:

Oracle APEX API Reference

Parent topic: Managing Session State Values