How Do I Use PL/SQL Variables in APEX

 Introduction

Using PL/SQL variables in Oracle APEX allows you to store, manipulate, and pass values within your application logic. These variables are useful in page processes, validations, computations, and dynamic actions where you need to work with data temporarily without storing it in a table. Understanding how to declare, assign, and use PL/SQL variables helps you build smarter, more responsive APEX applications with better control over business rules and data flow.

Using PL/SQL variables in Oracle APEX is an important technique for storing temporary data, performing calculations, or controlling logic flow. These variables allow you to work with values during execution without writing them to the database. You can declare PL/SQL variables in page processes, computations, validations, dynamic actions (server-side), or PL/SQL anonymous blocks.

Step 1: Where to Use PL/SQL Variables in APEX
PL/SQL variables can be used in the following places inside Oracle APEX:

• Page processes (Before Submit, After Submit)

• PL/SQL Dynamic Content regions

• Page computations

• Server-side conditions in dynamic actions

• Validations and branches

• PL/SQL function body returning value

Step 2: Declare a Variable
To use a PL/SQL variable, start by declaring it. In any PL/SQL block, use the DECLARE section:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  -- logic here
END;

If you're working inside a Page Process or Computation, APEX automatically wraps your code in a PL/SQL block, so you can start directly with the variable declaration.

Step 3: Assign a Value to the Variable
You can assign values using the := operator or with a SELECT INTO query:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 101;
  -- Optional: output result
  APEX_DEBUG.MESSAGE('Name: ' || v_name);
END;

Step 4: Use the Variable in Logic
Once a value is stored in the variable, you can use it in IF conditions, loops, or any logic block:

DECLARE
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = :P1_EMP_ID;

  IF v_salary > 5000 THEN
    APEX_DEBUG.MESSAGE('High salary');
  ELSE
    APEX_DEBUG.MESSAGE('Normal salary');
  END IF;
END;

Step 5: Passing Data from Page Items to Variables
Use colon : notation to reference APEX page items:

DECLARE
  v_email VARCHAR2(100);
BEGIN
  v_email := :P1_EMAIL;
  -- You can now use v_email in any logic
END;

Step 6: Returning Values to Page Items
You can assign values from variables back to APEX items like this:

:P1_STATUS := 'ACTIVE';

This sets the value of the page item P1_STATUS based on logic inside your PL/SQL code.

Step 7: Example – Full Use in a Page Process

DECLARE

  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM orders WHERE customer_id = :P1_CUSTOMER_ID;

  IF v_total > 0 THEN
    :P1_ORDER_COUNT := v_total;
  ELSE
    :P1_ORDER_COUNT := 0;
  END IF;
END;

Best Practices

• Always use meaningful variable names.

• Keep variable scope limited to what is necessary.

• Use %TYPE or %ROWTYPE to match variable data types with table columns.

• Use exception handling for SELECT INTO queries to avoid runtime errors.

• Use APEX_DEBUG.MESSAGE or DBMS_OUTPUT.PUT_LINE (in SQL Workshop) to debug.

Using PL/SQL variables in APEX allows you to write dynamic logic, respond to user input, and perform custom actions efficiently without hardcoding values or creating unnecessary database columns. It makes your applications more flexible, intelligent, and easier to maintain.

Conclusion

PL/SQL variables give you the flexibility to handle dynamic logic within your APEX pages and applications. By using them effectively, you can simplify processes, reduce the need for repeated queries, and improve performance. Whether you're using variables to calculate values, validate inputs, or pass data between procedures, they are a core part of efficient PL/SQL programming in Oracle APEX.

ADDITIONAL EXAMPLE: 

PL/SQL allows declaring variables to store and manipulate data within a session.

DECLARE

    v_salary NUMBER(10,2);

BEGIN

    SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    :P1_SALARY := v_salary;  -- Assigns value to APEX page item

END;

This example retrieves the employee’s salary and assigns it to an APEX page item (P1_SALARY).