How Do I Add Validations and Error Message

Validations and Error Messages in Oracle APEX

Validations in Oracle APEX ensure that user inputs meet specific criteria before processing the data. If a validation fails, an error message is displayed to the user, preventing incorrect or incomplete data from being saved.

You can use validations to check data a user enters before processing. Once you create a validation and the associated error message, you can associate it with a specific item. You can choose to have validation error messages display inline (that is, on the page where the validation is performed) or on a separate error page.

Creating an inline error message involves these steps:

• Step 1 - Create a validation and specify error message text. 

• Step 2 - Associate the validation with a specific item.

This tutorial covers the different types of validations available in APEX, how to create them, and how to customize error messages.

Types of Validations in APEX

There are several types of validations in APEX, each suited for different scenarios.

1. Item-Level Validations

These validations apply to individual page items, such as text fields, number fields, or select lists.

Examples include:

• Checking if a field is empty

• Validating email format

• Ensuring a number is within a specific range

2. Row-Level Validations

These validations apply to entire rows when using an Interactive Grid or Forms that insert or update database records.

Examples include:

• Ensuring a record is unique

• Checking if a value already exists in the database

3. Processing-Level Validations

These validations apply when submitting data through a process, such as an INSERT or UPDATE statement.

Examples include:

• Ensuring business rules are followed

• Validating multiple fields together

Creating Validations in APEX

Step 1: Adding a Required Field Validation

1. Open Page Designer in APEX.

2. Select the page item (e.g., P1_EMAIL).

3. Under the Validation section, click Create.

4. Choose Not Null as the validation type.

5. Enter a custom error message, such as:

"Email address cannot be empty. Please enter an email."

6. Click OK, then Save and Run the Page.

If the field is left empty and the user tries to submit the form, the error message will be displayed.

Step 2: Validating an Email Format

1. Create a new validation on the P1_EMAIL item.

2. Select Regular Expression as the validation type.

3. Use the following regex pattern to validate email format:

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

4. Enter an error message such as:

"Invalid email format. Please enter a valid email address."

5. Click Save and Run the Page, then test by entering an incorrect email format.

Step 3: Checking If a Value Already Exists in the Database

To prevent duplicate records, create a validation that checks if a value already exists in the database.

1. Open Page Designer and select the item, such as P1_USERNAME.

2. Under the Validation section, click Create.

3. Select PL/SQL Function Returning Boolean.

4. Enter the following PL/SQL code:

DECLARE

    v_count NUMBER;

BEGIN

    SELECT COUNT(*) INTO v_count 

    FROM USERS 

    WHERE USERNAME = :P1_USERNAME;

    RETURN v_count = 0;

END;

5. Enter an error message, such as:

"This username is already taken. Please choose another."

6. Click Save and Run the Page, then test by entering a duplicate username.

Customizing Error Messages

Step 1: Using a Friendly Error Display

1. Open Shared Components → Messages.

2. Click Create and enter an error code, such as ERR_DUPLICATE_USERNAME.

3. Enter a custom error message, such as:

"This username is already registered. Try another name."

4. Modify your validation to return this error code instead of a static message.

Step 2: Using JavaScript to Display Error Messages

To show a custom error message dynamically using JavaScript, follow these steps:

1. Add a Dynamic Action triggered on button click.

2. Set the action to Execute JavaScript Code.

3. Enter the following code to display an alert if a field is empty:

if (!$('#P1_USERNAME').val()) {

    apex.message.alert('Please enter a username before submitting.');

}

4. Click Save and Run the Page, then test by clicking the button without entering a username.

Best Practices for Using Validations in APEX

• Use Not Null validations for required fields to ensure they are always filled.

• Apply Regular Expressions for format validation, such as emails or phone numbers.

• Use PL/SQL Validations for database checks, such as ensuring uniqueness.

• Display friendly error messages to guide users instead of generic error codes.

• Use JavaScript for real-time feedback before form submission.

Validations in Oracle APEX help enforce data integrity and improve user experience by ensuring inputs meet specific requirements. By combining item-level, row-level, and process-level validations, along with custom error messages, you can create a more reliable and user-friendly application.