How do I Get the User name Using PL/sql Expression

 Introduction
In Oracle APEX, knowing the currently logged-in user is essential for customizing the user experience, enforcing security, or logging actions. Whether you're tracking who made changes to a record or filtering data based on the user, you’ll often need to retrieve the username using PL/SQL. Oracle APEX provides simple ways to access this information using built-in functions that work well within PL/SQL expressions.

Detailed Instructions: How to Get the Username Using PL/SQL in Oracle APEX

To retrieve the current user's username in a PL/SQL expression, use the :APP_USER bind variable or the V function.

1. Use the :APP_USER Bind Variable

   • This is the most direct and commonly used approach inside APEX pages or PL/SQL code blocks.

   • Example:

     v_username := :APP_USER;
     

   • You can use this in processes, validations, computations, or triggers to capture the username of the logged-in session.

2. Use V('APP_USER') in SQL Queries

   • If you're writing SQL or dynamic SQL inside PL/SQL, use the V() function.

   • Example:

     SELECT * FROM AUDIT_LOG WHERE USERNAME = V('APP_USER');
     

   • This is useful for filtering data shown to the current user.

3. Insert the Username into a Table

   • Suppose you’re tracking who submitted a form:

     INSERT INTO CHANGE_LOG (ID, CHANGED_BY, CHANGE_DATE)
     VALUES (SEQ_LOG.NEXTVAL, :APP_USER, SYSDATE);
     

4. Use in Default Value for Items

   • You can set a page item default type to PL/SQL Expression and use:

     :APP_USER
     

   • This will auto-fill a username field when the page loads.

5. Use in Logging Procedures

   • For centralized logging:

     PROCEDURE log_action(p_action IN VARCHAR2) IS
     BEGIN
       INSERT INTO LOG_TABLE (username, action, log_time)
       VALUES (:APP_USER, p_action, SYSTIMESTAMP);
     END;
     

 

Getting the User Name Using PL/SQL Expression in Oracle APEX

Retrieving the currently logged-in username in Oracle APEX is essential for tracking user actions, personalizing content, and managing security. This tutorial explains how to obtain the username using PL/SQL expressions within different APEX components.

Using the APP_USER Variable

Oracle APEX automatically provides the APP_USER variable, which holds the username of the currently logged-in user. This variable can be used in different places, such as SQL queries, PL/SQL processes, and page items.

Method 1: Using PL/SQL Expression in a Page Item

1. Open Page Designer in your APEX application.

2. Create a new page item (e.g., P1_USERNAME) of type Display Only.

3. Set the Value Type to PL/SQL Expression.

4. In the PL/SQL Expression field, enter:

:APP_USER

5. Save and run the page. The page item will now display the logged-in username.

Method 2: Using APP_USER in a SQL Query

You can use APP_USER in SQL queries to filter data based on the logged-in user.

Example: Retrieve employee details for the logged-in user

SELECT employee_id, name, department

FROM employees

WHERE username = :APP_USER;

This ensures that each user sees only their own records.

Method 3: Using APP_USER in PL/SQL Process

You can use APP_USER within a PL/SQL Process to store the username in a table or log user activity.

Example: Inserting the logged-in user's activity

INSERT INTO user_logs (username, login_time, action)

VALUES (:APP_USER, SYSDATE, 'Page Accessed');

This records every instance when a user accesses a page.

Method 4: Using APP_USER in a PL/SQL Function Returning a Value

If you need to use APP_USER in a function for validation or business logic, you can define a PL/SQL function as follows:

FUNCTION get_current_user RETURN VARCHAR2 IS

BEGIN

   RETURN :APP_USER;

END get_current_user;

This function can be used in different PL/SQL components in your application.

Method 5: Displaying the Username in a Header or Footer

1. Go to Shared Components > User Interface > Breadcrumbs, Headers, or Footers.

2. Add the following PL/SQL block in the appropriate section:

Welcome, <b> &APP_USER. </b>

This dynamically displays the username in the header.

Best Practices

• Use APP_USER for user-specific filtering and logging.

• Store APP_USER in a table if you need a historical log of user activities.

• Always validate user permissions before displaying data based on APP_USER.

• Use APP_USER in combination with authorization schemes for secure access control.

The APP_USER variable in Oracle APEX makes it easy to retrieve the logged-in username using PL/SQL expressions. Whether you are storing user activity, filtering reports, or personalizing content, APP_USER ensures that your application dynamically adapts to each user's session.

EXAMPLE:

The key here is to use  the PL/SQL EXPRESSION: apex_custom_auth.get_username

In this example we will save the value into a text box and call the expression at the time that the page loads.

Step 1 – Add a field to the page

Step 2- Add a dynamic action

• In this case, in the Event> Page Load.

• Set the Action: Set Value

• Set Settings > Settings: PL/SQL Expression

• Set Settings > PL/SQL Expression: apex_custom_auth.get_username

• Set Settings > Items To Submit: P31_NEW (the name of your field)

• Set Affected Elements > Selection Type: Item(s)

• Set Affected Elements > P31_NEW (Name of your Text Field)

Here is the result. The name of the user (In this case Test2) is displayed in the box

Conclusion
Retrieving the current user's username using PL/SQL in Oracle APEX is straightforward and highly useful for personalizing and securing your application. The :APP_USER bind variable and V('APP_USER') function give you full access to session identity, allowing you to track, filter, and respond to users dynamically. Whether you're inserting audit records, limiting data access, or pre-populating fields, capturing the username with PL/SQL is a core practice every APEX developer should master.