Oracle APEX: How to Dynamically Fetch User-Specific Data

 Oracle Apex

• Oracle APEX is a low-code development platform that enables developers to build scalable, secure enterprise applications with world-class features that can be deployed anywhere. It allows developers to quickly create and deliver powerful applications that can solve real world problems and provide effective business solutions.

Purpose

In enterprise applications, it is critical that each user sees only the data they are authorized to access.

This article demonstrates how to dynamically fetch data (e.g.,vehicle details) based on the currently logged-in user in Oracle APEX.

Prerequisites

Before following this guide, make sure you have the following in place:

•     An existing APEX application with at least one page

•    An Interactive Grid or Interactive Report region displaying data (e.g., Vehicle Details)

•   Developer access to App Builder for the application 

Overview

Retrieve the employee ID based on the currently logged-in user (:APP_USER) Store the employee ID in a hidden page item (P398_EMP_ID) Use the employee ID to fetch the corresponding vehicle ID (P398_VEHICLE_ID) Filter the report or Interactive Grid using the retrieved vehicle ID

 Implementation Steps

Step 1: Create a Hidden Page Item for Employee ID

Create a hidden item to store Employee ID

Hidden page items are used to store intermediate values during page processing. They are not visible to the user but can be referenced in SQL queries and dynamic actions using bind variable syntax.

In the Page Designer, create a new page item with the following configuration:

• Name: P398_EMP_ID
• Type: Hidden
• Value Protected: No (so that Dynamic Actions can set its value)

 

Step 2: Set the Source of P398_EMP_ID to Fetch the Logged-In User

 Default --- Type: SQL Query

Use the following SQL to get the logged-in user

SELECT m.employee_id 
FROM user_m m
WHERE LOWER(m.user_desc) = LOWER(:APP_USER)

 Key points:

•        :APP_USER is a built-in APEX bind variable that contains the authenticated session username.

•        The LOWER() function on both sides ensures case-insensitive matching, which is important if usernames are stored in mixed case.

 

Step 3: Create a Dynamic Action Under Page Item P398_EMP_ID

Create a Dynamic Action that triggers when the value of P398_EMP_ID changes

 Create a Dynamic Action with the following settings:

•        Name: Employee id

•        Event: Change

•        Selection Type: Item

•        Item: P398_EMP_ID

Action  

Now define the True Action uses a PL/SQL Expression to push the value of P398_EMP_ID into session state so it is available as a bind variable for the SQL query in True Action

•        Action: Set Value

•       Set Type: PL/SQL Expression

Enter the following as the PL/SQL Expression:

:P398_EMP_ID

Items to Submit: P398_EMP_ID

   •     Affected Elements

Select Type:  Item(s)

Item(s): P398_EMP_ID

 

Step 4: Create the Vehicle ID Hidden Page Item

Create a second hidden item to store the Vehicle ID that will be resolved in the next step:

• Item Name: P398_VEHICLE_ID
• Type : Hidden
•  Value Protected: No

 

Step 5: Create a Dynamic Action to Fetch the Vehicle ID

Dynamic actions enable developers to define complex client-side behavior declaratively without the need for JavaScript.

Purpose: Fetch vehicle ID for the logged-in employee

Create a Dynamic Action with the following settings:

•        Name: Set Vehicle ID

•        Event: Page Load

 

Under the Action, configure as follows:

•        Action: Set Value

•        Set Type: SQL Statement

Use following SQL statement

 SELECT d.vehicle_id

FROM vehicle d

WHERE d.employee_id = :P398_EMP_ID
  AND d.status = 'A'

Explanation:

•        d.employee_id = :P398_EMP_ID — Filters vehicles to only those belonging to the logged-in employee.

•        d.status = 'A'  ----Restricts results to active vehicles only.

 

•       Items to Submit: P398_EMP_ID

•       Affected Element

Selection Type : Item(s)

Item(s): P398_VEHICLE_ID

 Step 6: Create a Region to display Data

With both hidden items populated, you can now use them as bind variables in report. Add the following condition to your region's WHERE clause

Apply the filter in your report or region:

SELECT v.vehicle_id,

       v.make,

       v.model,

       v.registration_number,

       v.assigned_date

  FROM vehicles v

 WHERE v.vehicle_id = :P398_VEHICLE_ID

This filter ensures that the report only ever shows records associated with the vehicle of the currently logged-in user. 

Items to Submit: P398_VEHICLE_ID, P398_EMP_ID

After completing all configurations, run and test the page. The following screenshot shows the output:

Conclusion

In this article, we demonstrated a simple and efficient approach to implementing user-specific data filtering in Oracle APEX. By leveraging hidden page items, bind variables, and Dynamic Actions, developers can ensure that each user only accesses data relevant to them. This technique enhances both data security and user experience while remaining easy to implement and maintain.