Using PL/SQL for APEX Validations

 Introduction

Using PL/SQL for validations in Oracle APEX allows you to apply powerful and flexible business rules to your forms and processes. PL/SQL validations let you check data inputs, enforce conditions, and display meaningful error messages before the data is processed or saved. This helps ensure that only correct and complete information is submitted, improving data accuracy and application reliability.

How Do I Use PL/SQL for APEX Validations
Font: Arial, 14px, Plain Text

In Oracle APEX, PL/SQL validations allow you to apply custom logic to check user input before it is accepted and processed. This helps ensure data accuracy, apply business rules, and improve application behavior. You can create PL/SQL-based validations on buttons, page items, or conditions that must be met before submitting data.

Step 1: Go to the Page Designer
Open your Oracle APEX application and navigate to the page where you want to apply validation. In the left pane, expand the "Validations" section under the page you’re editing.

Step 2: Create a New Validation
Click the "Create" button inside the Validations section. Choose the type of validation you want to create. Select PL/SQL Function (returning Boolean) or PL/SQL Expression depending on your use case.

Step 3: Choose Validation Level
Choose where the validation should apply:

• Item-level: Checks one specific item

• Page-level: Checks multiple items or custom logic across the page

Step 4: Write PL/SQL Code for Validation
Use a PL/SQL function body that returns TRUE or FALSE. Return TRUE if the input is valid. Return FALSE (or raise an error) if the input is invalid.

Example 1 – PL/SQL Function Body (Page-level):

BEGIN
  IF :P1_AGE < 18 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END;

Example 2 – PL/SQL Expression (simpler syntax):

:P1_SALARY > 0

Step 5: Add an Error Message
After entering the PL/SQL logic, specify the message that should appear if the validation fails. This message is shown to the user on the page.

Step 6: Set When Validation Fires
In the settings, choose when the validation should be triggered. You can link it to a specific button or have it run every time the page is submitted. You can also set conditions if the validation should run only under certain circumstances.

Step 7: Test the Validation
Run the page, enter values that break the rule, and make sure the error message appears. Then enter valid data and confirm that the form proceeds as expected.

Best Practices

• Keep validation logic clear and short.

• Use page item references like :P1_ITEM_NAME.

• Always provide meaningful and user-friendly error messages.

• Combine PL/SQL with SQL queries for advanced checks.

• Use APEX_DEBUG.MESSAGE for troubleshooting during development.

PL/SQL validations in Oracle APEX are a powerful way to enforce complex rules that cannot be handled by simple declarative options. They give you full control over input logic and allow you to keep your application data clean and consistent.

Conclusion
PL/SQL-based validations are a critical part of building secure and user-friendly Oracle APEX applications. By leveraging custom logic, you can enforce complex rules that go beyond built-in validations. Using PL/SQL for validations not only improves data quality but also gives you full control over how and when errors are detected and handled in your application.

Additional Example

PL/SQL is commonly used in validations to enforce data integrity.

Example of a Validation Process:

DECLARE

    v_count NUMBER;

BEGIN

    SELECT COUNT(*) INTO v_count FROM EMPLOYEES WHERE EMPLOYEE_ID = :P1_EMP_ID;

    IF v_count = 0 THEN

        RAISE_APPLICATION_ERROR(-20001, 'Employee does not exist.');

    END IF;

END;

If the employee ID does not exist, an error message is displayed.