8.4 Understanding Validations

A validation is an edit check. Validations specific to a single item are page item validations. Validations that apply to an entire page are page validations. Validations for tabular forms specific to a single column are column level validations. Validations for tabular forms that do not apply to a single column are tabular form row validations.

You can define a validation declaratively by selecting a validation method. You enter the actual validation edit check in the Validation Messages field. Be aware that if a validation fails, subsequent page processes or computations do not occur. Also remember that the validation you enter must be consistent with the validation type you selected.

Topics:

8.4.1 Creating Item and Page Validations

You create a validation by running the Create Validation Wizard. This wizard changes slightly depending upon what you are validating. Validations are either specific to a given field (either a single page item or tabular form column validation) or, they are not field specific (either a page or tabular form row validation).

Topics:

8.4.1.1 Creating a Validation for a Page Item

To create a validation for a page item:

Note:

Text entered for validations may not exceed 3,950 characters.
  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Create Validation Wizard:

    • Tree view - Under Page Processing, right-click Validating and select Create Validation.

    • Component view - Under Page Processing, locate Validations. Click the Create icon.

    The Create Validation Wizard appears.

  3. For Validation Level, select Page Item and click Next.

  4. For Item/Column, select the item to be validated and click Next.

  5. For Sequence and Name:

    1. Sequence - Enter the sequence number for this item.

    2. Validation Name - Enter a name for the validation.

    3. Error Display Location - Select the display location of the validation error message. Validation error messages can display on an error page or inline with the submitted page. Inline validations can display in the notification area (defined as part of the page template) or within the field label.

    4. Click Next.

  6. For Validation Type, select a method as described in Table 8-10 and click Next.

    Table 8-10 Page Item Validation Methods

    Validation Method Descriptions

    Not Null

    Checks if an item's value in session state is null.

    For example, you could validate that the user enters a value in a field by creating an item validation and then selecting the validation method Item Not Null.

    String Comparison

    Compares the value of an item to a specific string. When prompted select a validation type.

    There are several string comparison validations that compare the value of an item to a literal string. For example, you select the validation type Item in Expression 1 is contained in Expression 2 to validate a user entry in a field against a list of values you provide.

    In Expression 1, enter the name of the item you want to validate without a colon. For example:

    P1_VALUE
    

    In Expression 2, enter a string of values you want to validate against. For example:

    ABC/DEF/GHI
    

    Regular Expression

    Regular expressions provide a method to describe text patterns. Use a Regular Expression validation to perform data validation. When prompted, enter a regular expression.

    For example, you could use the following regular expression validation to verify that a string of entered data always consists of groups of six numbers separated by commas and followed by a comma:

    î([[:digit:]]{6},)+$
    

    This regular expression would find the following entries valid:

    123456,654321,

    123456,

    123456,123456,654321,

    However, the following would not be valid:

    123456,12345

    12345

    SQL

    Compares to values in the database. Select a validation type:

    • Exists

    • NOT Exists

    • SQL Expression

    For example, you can use a SQL validation to verify whether a last name typed into a field exists in the database. In the following Exists SQL validation, the field is named P1_LAST_NAME and the table is named customers.

    SELECT 1 FROM customers 
    WHERE last_name = :P1_LAST_NAME
    

    To learn more, see the syntax examples at the bottom of the page.

    PL/SQL

    Useful if you need complex logic. Select a validation type:

    • PL/SQL Expression

    • PL/SQL Error

    • Function Returning Boolean

    • Function Returning Error Text

    For example, suppose you need to create a validation for an address form that requires the user to enter a province if the address is not in the United States. You could create the validation as a Function Returning Boolean, using the following PL/SQL:

    BEGIN
      IF :P1_COUNTRY = 'US' AND :P1_PROVINCE IS NULL THEN
         RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
    END;
    

    You could also create the same validation implemented as a PL/SQL Expression as follows:

    NOT (:P1_COUNTRY='US' AND  :P1_PROVINCE IS NULL);
    

    To learn more, see the syntax examples at the bottom of the page.


  7. For Validation:

    1. Error Message - Depending upon the validation method, enter the validation or message text that displays if the validation fails.

      You can use #LABEL# as placeholder for the label of the associated page item.

    2. Always Execute - Determines if validations execute when a page is submitted. Use this attribute with the Execute Validations attribute for buttons or certain page items that submit a page. Option include:

      • Yes - The validation always executes independent of validation settings for buttons or items on a page. Note that defined conditions still apply.

      • No - The validation only executes if the button or item used to submit the page has the Execute Validations attributes set to Yes.

      To learn more, see "Determining When Validations Execute".

    3. Click Next.

  8. Define conditions that apply to this validation and click Create.

8.4.1.2 Creating a Validation for an Entire Page

To create a validation for an entire page:

Note:

Text entered for validations may not exceed 3,950 characters.
  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. To access the Create Validation Wizard:

    • Tree view - Under Page Processing, right-click Validating and select Create Validation.

    • Component view - Under Page Processing, locate Validations. Click the Create icon.

    The Create Validation Wizard appears.

  3. For Validation Level, select Page and click Next:

  4. For Sequence and Name:

    1. Sequence - Enter the sequence number for this item.

    2. Validation Name - Enter a name for the validation.

    3. Error Display Location - Select the display location of the validation error message. Validation error messages can display on an error page or inline with the submitted page. Inline validations can display in the notification area (defined as part of the page template) or within the field label.

    4. Click Next.

  5. For Validation Type, select a method as described in Table 8-11 and click Next.

    Table 8-11 Page Validation Methods

    Validation Method Descriptions

    SQL

    Compares to values in the database. Select a validation type:

    • Exists

    • NOT Exists

    • SQL Expression

    To learn more, see the syntax examples at the bottom of the page.

    PL/SQL

    Useful if you need complex logic. Select a validation type:

    • PL/SQL Expression

    • PL/SQL Error

    • Function Returning Boolean

    • Function Returning Error Text

    To learn more, see the syntax examples at the bottom of the page.


  6. For Validation:

    1. Validation Code - Enter the code corresponding to the validation type selected.

    2. Error Message - Depending upon the validation method, enter the validation or message text that displays if the validation fails.

      You can use #LABEL# as placeholder for the label of the associated page item.

    3. Always Execute - Determines if validations execute when a page is submitted. Use this attribute with the Execute Validations attribute for buttons or certain page items that submit a page. Option include:

      • Yes - The validation always executes independent of validation settings for buttons or items on a page. Note that defined conditions still apply.

      • No - The validation only executes if the button or item used to submit the page has the Execute Validations attributes set to Yes.

      To learn more, see "Determining When Validations Execute".

    4. Click Next.

  7. Define conditions that apply to this validation and click Create.

8.4.1.3 Creating a Validation for a Tabular Form Column

To create a validation for a tabular form column:

Note:

Text entered for validations may not exceed 3,950 characters.
  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. To access the Create Validation Wizard:

    • Tree view - Under Page Processing, right-click Validating and select Create Validation.

    • Component view - Under Page Processing, locate Validations. Click the Create icon.

    The Create Validation Wizard appears.

  3. For Validation Level:

    1. Tabular Form - Select the associated tabular form region for this validation.

    2. Identify the validation level - Select Column.

    3. Select the column to be validated.

  4. For Item/Column:

    1. Select the column to be validated.

    2. Click Next.

  5. For Sequence and Name:

    1. Sequence - Enter the sequence number for this item.

    2. Validation Name - Enter a name for the validation.

    3. Error Display Location - Select the display location of the validation error message. Validation error messages can display on an error page or inline with the submitted page. Inline validations can display in the notification area (defined as part of the page template) or within the field label.

    4. Click Next.

  6. For Validation Type, select a method as described in Table 8-12. Follow the on-screen instructions and click Next.

    Table 8-12 Validation Methods for Tabular Form Column

    Validation Method Descriptions

    Not Null

    Checks if the item's value in session state is null.

    String Comparison

    Compares the value of an item to a specific string. When prompted select a validation type.

    Regular Expression

    Regular expressions provide a method to describe text patterns. Use a Regular Expression validation to perform data validation. When prompted, enter a regular expression.

    SQL

    Compares to values in the database. Select a validation type:

    • Exists

    • NOT Exists

    • SQL Expression

    To learn more, see the syntax examples at the bottom of the page.

    PL/SQL

    Useful if you need complex logic. Select a validation type:

    • PL/SQL Expression

    • PL/SQL Error

    • Function Returning Boolean

    • Function Returning Error Text

    To learn more, see the syntax examples at the bottom of the page.


  7. For Validation:

    1. Error Message - Depending upon the validation method, enter the validation or message text that displays if the validation fails.

      You can use #COLUMN_HEADER# as placeholder for the column header of the associated tabular form column.

    2. Always Execute - Determines if validations execute when a page is submitted. Use this attribute with the Execute Validations attribute for buttons or certain page items that submit a page. Option include:

      • Yes - The validation always executes independent of validation settings for buttons or items on a page. Note that defined conditions still apply.

      • No - The validation only executes if the button or item used to submit the page has the Execute Validations attributes set to Yes.

      To learn more, see "Determining When Validations Execute".

    3. Click Next.

  8. Define conditions that apply to this validation and click Create.

8.4.1.4 Creating a Validation for a Tabular Form Row

To create a validation for a tabular form row:

Note:

Text entered for validations may not exceed 3,950 characters.
  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Access the Create Validation Wizard:

    • Tree view - Under Page Processing, right-click Validating and select Create Validation.

    • Component view - Under Page Processing, locate Validations. Click the Create icon.

    The Create Validation Wizard appears.

  3. For Validation Level:

    1. Tabular Form - Select the associated tabular form region for this Validation.

    2. Identify the validation level - Select Tabular Form Row.

    3. Click Next.

  4. For Sequence and Name specify the following and click Next.

    • Sequence - Enter the sequence number for this item.

    • Validation Name - Enter a name for the validation.

    • Error Display Location - Select the display location of the validation error message. Validation error messages can display on an error page or inline with the submitted page. Inline validations can display in the notification area (defined as part of the page template) or within the field label.

  5. For Validation Type, select a method as described in Table 8-13.

    Table 8-13 Validation Methods for Tabular Form Row

    Validation Method Descriptions

    SQL

    Compares to values in the database. Select a validation type:

    • Exists

    • NOT Exists

    • SQL Expression

    To learn more, see the syntax examples at the bottom of the page.

    PL/SQL

    Useful if you need complex logic. Select a validation type:

    • PL/SQL Expression

    • PL/SQL Error

    • Function Returning Boolean

    • Function Returning Error Text

    To learn more, see the syntax examples at the bottom of the page.


  6. For Validation:

    1. Validation Code - Enter the code corresponding to the validation type selected.

    2. Error Message - Depending upon the validation method, enter the validation or message text that displays if the validation fails.

      You can use #COLUMN_HEADER# as placeholder for the column header of the associated tabular form column.

    3. Always Execute - Determines if validations execute when a page is submitted. Use this attribute with the Execute Validations attribute for buttons or certain page items that submit a page. Option include:

      • Yes - The validation always executes independent of validation settings for buttons or items on a page. Note that defined conditions still apply.

      • No - The validation only executes if the button or item used to submit the page has the Execute Validations attributes set to Yes.

      To learn more, see "Determining When Validations Execute".

    4. Click Next.

  7. Define conditions that apply to this validation and click Create.

8.4.2 Determining When Validations Execute

When creating a validation, the Always Execute attribute determines if validations execute when a page is submitted. Use this attribute with the Execute Validations attribute for buttons or certain page items that submit a page. This attribute has two options:

  • Yes - The validation always executes independent of validation settings for buttons or items on a page.

    For example, select Yes if your validation is a security check which determines if the current user is allowed to modify or delete the current record. This ensures the validation executes for any operation and prevents unprivileged users from modifying data.

  • No - The validation only executes if the button or item used to submit the page has the Execute Validations attributes set to Yes.

    For example, it is not useful to verify that an item be numeric and display an error message if the user is simply deleting a record. In that example, the validations should only execute when the user clicks the Create or Apply Changes buttons.

    To accomplish this behavior:

    • For the validation, set Always Execute to No

    • For the Create and Apply Changes buttons, set Execute Validations to Yes.

    • For the Delete button, set Execute Validations to No to avoid unnecessary validations

8.4.3 Defining How Validation Error Messages Display

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.

To define how a validation error message displays:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Go to the Edit Validations page:

    • Tree view - Under Page Processing, locate Validating and expand the Validations branch. Right-click the validation name and select Edit.

    • Component view - Under Validations, select the appropriate validation.

  3. Scroll down to Error Message.

  4. In Error Message, enter the error message text. See item Help, for more details.

    Validation error messages can display on an error page or inline within the existing page.

  5. From Error message display, select one of the following:

    • Inline with Field and in Notification - Error displays inline within the field label and within a notification area (defined as part of the page template).

    • Inline with Field - Error displays inline within the field label.

    • Inline in Notification - Error displays inline within a notification area (defined as part of the page template).

    • On Error Page - Displays the error on a separate error page. To create a hard error that stops all processing (including validations), you must display the error on an error page.

    If you select Inline with Field or Inline with Field and in Notification, you must associate an item with the error message. From the Associated Item list, select an item.

    Tip:

    If you select Inline with Field or Inline with Field and in Notification, be aware that the Application Express engine does not execute computations or processes during the re-rendering of the page when the validation error messages appear.
  6. Click Apply Changes.

See Also:

"Page Templates"

8.4.3.1 About the Go to Error Link

If you define an associated item, Oracle Application Express renders a Go to Error link in the Notification display location next to the corresponding error message. When clicked, the user's focus is set to the associated item.

You can alter the Go to Error text to change the default text or to translate it into a language that is not translated by default with Oracle Application Express. In either case, define a text message called APEX.GO_TO_ERROR with the text and language of your choice. Additionally, you can disable this functionality and remove the Go to Error link by setting the text of the APEX.GO_TO_ERROR text message to just a single space. To learn more, see "Translating Messages Used Internally by Oracle Application Express".

8.4.4 Processing Validations Conditionally

You can control when and if a validation is performed under Conditions.

To create a condition for an existing validation:

  1. Navigate to the appropriate Page Definition. See "Accessing the Page Definition".

  2. Go the Edit Validations page:

    • Tree view - Under Page Processing, locate Validating and expand the Validations branch. Right-click the validation name and select Edit.

    • Component view - Under Validations, select the appropriate validation.

  3. Scroll down to Conditions.

  4. Under Condition, specify the following:

    1. When Button Pressed - Select a page button. If a user clicks the button you select, the validation is performed (that is, depending upon to other defined conditions). This validation is performed if the page is submitted by a means other than the clicking the specified button.

      If no button is pressed, other conditions are evaluated before performing the validation.

    2. Execution Scope - Applicable only to tabular forms. Specify the execution scope:

      • For Created and Modified Rows - The validation executes only for created and modified rows in your tabular form.

      • All Submitted Rows - The validation executes for every submitted row.

    3. Condition Type - Select a condition type.

      • Expression Attributes - Depending upon the selected Condition Type, enter values in the Expression attributes.

      • Execute Condition - Applicable only to tabular forms. Select either:

        For Each Row - Select this option if you refer to tabular form columns in your condition. This option causes the condition to evaluate each row of the tabular form.

        Once - Select this option if you do not refer to tabular form specific values.

      The validation is rendered or processed if the specified condition is met.

  5. Click Apply Changes.

8.4.5 What Happens When a Validation Fails?

If a page is submitted and some of the validations fail, Oracle Application Express redisplays the existing page with all inline validation errors. While displaying inline errors, Oracle Application Express does not execute computations, application processes, or page processes (for example, Automated Row Fetch) which are defined to execute during Page Rendering (in other words, all "On Load %" display points). The one exception is if the computations and processes use the condition type Inline Validation Errors Displayed.

The reason for skipping these computations and processes is that any of they could potentially alter the data entered by the user. For example, an Automated Row Fetch process would fetch and overwrite the modified data with the values from the database when the user actually wants to get the entered data in order to fix the validation error.

If a computation is skipped, the following displays in the debug output:

Do not perform computation because inline validation errors found.

If an application or page process is skipped, the following displays in the debug output:

Skip because inline validation errors found.