Validation Methods

Validations are written to return a TRUE or FALSE value.  A return value of TRUE means the validation is successful.  A return value of FALSE means there is as an error.

SQL

• Exists:   Allows the creation of a SQL select statement that checks for the existence of data in the database.  The validation will return TRUE if at least one row is returned from the query.

Example:

select 1 from inventory_items
where  item_id = :P100_ITEM_ID
and    qty_available >= :P100_QTY_ORDERED;

• NOT Exists:   This is a good validation to use to prevent the insert of a record that already exists.  If a row is found, the validation returns a false value indicating an error.  The syntax is the same as for the Exists example above.

• SQL Expression:   Evaluates a SQL or PL/SQL expression and returns a TRUE or FALSE value of the expression.  This type of validation is valuable, for example, checking that a birth date is not some date in the future.  It is also valuable for comparing numeric values since page items are displayed in alphanumeric format.

Example:

 :P100_BIRTH_DATE <= SYSDATE

Example:

 TO_NUMBER(:P100_AMOUNT_OWED)
         = TO_NUMBER(:P100_AMOUNT_PAID)