Setting Item Value in Session Using PL/SQL in Oracle APEX

October 21, 2016Posted inAPEX

Using APEX_UTIL.SET_SESSION_STATE

Oracle APEX provides a built-in package APEX_UTIL that includes a procedure SET_SESSION_STATE for setting the value of items in the session state. Here’s the syntax:

Copy Code
APEX_UTIL.SET_SESSION_STATE (
    p_name  IN VARCHAR2,
    p_value IN VARCHAR2
);
  • p_name: The name of the item you want to set.
  • p_value: The value you want to assign to the item.

Example

Let’s consider an example where we want to set the value of a page item P1_DEPT to 12 using PL/SQL.

  1. Create a PL/SQL ProcessYou can create a PL/SQL process on a page or an application process. For this example, we’ll create a process on a specific page.
    • Navigate to the desired page in Oracle APEX.
    • Under the Processing section, click on Processes.
    • Click on Create and select PL/SQL.
  2. Define the PL/SQL CodeEnter the following PL/SQL code in the process:

Enter the following PL/SQL code in the process:

Copy Code
BEGIN
    APEX_UTIL.SET_SESSION_STATE('P1_CUSTOMER_ID', '12345');
END;

3. Save and Run

Save the process and run the page. The value of P1_DEPT will be set to 12 when this process is executed.

Now try to set it without loading the page.

Using PL/SQL Block in Dynamic Actions

In addition to processes, you can also use PL/SQL blocks within dynamic actions to set item values. This can be particularly useful for setting values in response to user interactions such as button clicks

Example

Let’s create a dynamic action that sets the value of P1_DEPT to 12 when a button is clicked.

  1. Create a Dynamic Action
    • Go to the Dynamic Actions section of the page.
    • Click on Create and select Event as change.
    • Set the Selection Type to Item and choose Item D1_DEPT
  2. Add a True Action
    • Add a True Action of type Execute PL/SQL Code.
    • Enter the following PL/SQL code:
Copy Code
BEGIN
    APEX_UTIL.SET_SESSION_STATE('P1_DEPT', '12');
END;
Create Dynamic Action> Event > change Selection Type > Items>P1_DEPT
Action > Execute PL/SQL code >

Page Items to Submit > P1_DEPT Now type into the item.

 

See the Session State

Tags: