A Comprehensive Guide to APEX_ERROR.ADD_ERROR in Oracle APEX

Introduction to the APEX_ERROR Package

In Oracle APEX, error handling is a crucial part of building stable and user-friendly applications. The APEX_ERROR package provides powerful APIs to control, display, and customize error messages during process execution, validations, and complex forms like Interactive Grids or Tabular Forms.

The ADD_ERROR procedure in this package allows developers to manually add error messages to the session state, associate them with specific items or data columns, and define how and where they should appear on the page.

---

Overview of the Five ADD_ERROR Signatures

1. ADD_ERROR(p_message IN VARCHAR2)

2. ADD_ERROR(p_message IN VARCHAR2, p_display_location IN VARCHAR2)

3. ADD_ERROR(p_message IN VARCHAR2, p_additional_info IN VARCHAR2, p_display_location IN VARCHAR2)

4. ADD_ERROR(p_message IN VARCHAR2, p_additional_info IN VARCHAR2, p_display_location IN VARCHAR2, p_column_alias IN VARCHAR2)

5. ADD_ERROR(p_message IN VARCHAR2, p_additional_info IN VARCHAR2, p_display_location IN VARCHAR2, p_column_alias IN VARCHAR2, p_row_num IN NUMBER)

Let’s explore each signature with practical, detailed examples:

---

Signature 1: ADD_ERROR(p_message IN VARCHAR2)

Purpose:

Adds a basic error message that is not tied to any specific item or column.

Example:

BEGIN
  APEX_ERROR.ADD_ERROR(p_message => 'Please fill in the "Description" field.');
END;

Explanation:

This message appears in the page’s notification region (at the top) and is not associated with any form item.

---

Signature 2: ADD_ERROR(p_message, p_display_location)

Purpose:

Adds an error message and lets you control where it will be displayed.

Possible values for p_display_location:

• apex_error.c_inline_in_notification: Show in page notification area.

• apex_error.c_inline_with_field: Display next to the form field.

Example:

BEGIN
  APEX_ERROR.ADD_ERROR(
    p_message          => 'Invalid date value.',
    p_display_location => apex_error.c_inline_in_notification
  );
END;

Explanation:

If c_inline_with_field is used without a specific field/column, the message still appears in the top notification area.

---

Signature 3: ADD_ERROR(p_message, p_additional_info, p_display_location)

Purpose:

Adds an error message along with additional information for logging or debugging purposes.

Example:

BEGIN
  APEX_ERROR.ADD_ERROR(
    p_message         => 'Record insertion failed.',
    p_additional_info => 'ORA-00001: Unique constraint violation.',
    p_display_location => apex_error.c_inline_in_notification
  );
END;

Explanation:

Only p_message is shown to the end user. p_additional_info is stored internally and used in debugging or logs.

---

Signature 4:

ADD_ERROR(
  p_message          IN VARCHAR2,
  p_additional_info IN VARCHAR2,
  p_display_location IN VARCHAR2,
  p_column_alias     IN VARCHAR2)

Purpose:

Associates an error message with a specific column of a data source (like an Interactive Grid or a Form field).

Example:

BEGIN
  APEX_ERROR.ADD_ERROR(
    p_message          => 'Invalid date format.',
    p_additional_info  => 'User entered incorrect date format.',
    p_display_location => apex_error.c_inline_with_field,
    p_column_alias     => 'START_DATE'
  );
END;

Explanation:

This error will appear next to the START_DATE field. The p_column_alias must match the item name or column alias.

---

Signature 5:

ADD_ERROR(
  p_message          IN VARCHAR2,
  p_additional_info IN VARCHAR2,
  p_display_location IN VARCHAR2,
  p_column_alias     IN VARCHAR2,
  p_row_num          IN NUMBER)

Purpose:

Display a custom error for a specific row and column in an Interactive Grid or Tabular Form.

Practical Example:

BEGIN
  FOR rec IN (
    SELECT seq_id, c001 AS SALARY
    FROM apex_collections
    WHERE collection_name = 'EMP_GRID'
  ) LOOP
    IF TO_NUMBER(rec.salary) < 0 THEN
      APEX_ERROR.ADD_ERROR(
        p_message          => 'Salary cannot be negative.',
        p_additional_info  => 'Value entered: ' || rec.salary,
        p_display_location => apex_error.c_inline_with_field,
        p_column_alias     => 'SALARY',
        p_row_num          => rec.seq_id
      );
    END IF;
  END LOOP;
END;

Explanation:

This message is displayed inline with the SALARY column and the exact row where the error occurred in the Interactive Grid.

---

Comparison Table of Signatures

---

Combined Use Cases

Use Case 1: Email Validation

BEGIN
  IF :P1_EMAIL IS NULL THEN
    APEX_ERROR.ADD_ERROR(
      p_message          => 'Email is required.',
      p_display_location => apex_error.c_inline_with_field,
      p_column_alias     => 'P1_EMAIL'
    );
  ELSIF NOT REGEXP_LIKE(:P1_EMAIL, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN
    APEX_ERROR.ADD_ERROR(
      p_message          => 'Invalid email format.',
      p_display_location => apex_error.c_inline_with_field,
      p_column_alias     => 'P1_EMAIL'
    );
  END IF;
END;

Use Case 2: Duplicate Username Check

DECLARE
  l_exists BOOLEAN;
BEGIN
  SELECT COUNT(*) > 0 INTO l_exists
  FROM USERS
  WHERE username = :P1_USERNAME;

  IF l_exists THEN
    APEX_ERROR.ADD_ERROR(
      p_message          => 'This username is already taken.',
      p_additional_info  => 'USERNAME=' || :P1_USERNAME,
      p_display_location => apex_error.c_inline_with_field,
      p_column_alias     => 'P1_USERNAME'
    );
  END IF;
END;

---

Key Notes on ADD_ERROR

• When using apex_error.c_inline_with_field, you must supply a valid p_column_alias.

• In Interactive Grids, p_row_num starts from 1 and should match the APEX-generated row ID or collection row ID.

• In Forms, p_column_alias usually matches the item name (like P1_NAME) or the underlying column name.

• p_additional_info is not shown to the end user but is helpful for debugging and internal logging.

---

Conclusion

The ADD_ERROR procedure in the APEX_ERROR package is a powerful tool for error handling in Oracle APEX applications. With five different signatures, it provides full flexibility to handle basic and advanced error scenarios—ranging from simple form validations to Interactive Grid error row mapping.

By leveraging the correct signature and parameters, developers can deliver highly customized user feedback and better debugging insights.