Menu

Oracle VALIDATE_CONVERSION: Check Whether a Value Can Be Converted

Oracle VALIDATE_CONVERSION() checks whether an expression can be converted to a specified data type without actually returning the converted value.

It is useful when cleaning imported text, validating dates or numbers, and filtering bad data before calling conversion functions such as TO_NUMBER() or TO_DATE().

Syntax

VALIDATE_CONVERSION(
  expr AS type_name
  [, fmt [, 'nlsparam']]
)

Return Value

VALIDATE_CONVERSION() returns:

  • 1 if expr can be converted successfully
  • 0 if the conversion itself would fail
  • 1 if expr evaluates to SQL NULL

If evaluating expr raises an error before the conversion is attempted, Oracle propagates that error.

Supported Target Types

Oracle supports target types including:

  • BINARY_DOUBLE
  • BINARY_FLOAT
  • DATE
  • INTERVAL DAY TO SECOND
  • INTERVAL YEAR TO MONTH
  • NUMBER
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE

The exact valid source expressions and the meaning of fmt and nlsparam depend on the target type.

Validate a Number

SELECT
  VALIDATE_CONVERSION('123.45' AS NUMBER) AS valid_number,
  VALIDATE_CONVERSION('abc' AS NUMBER) AS invalid_number
FROM dual;

Conceptually:

VALID_NUMBER  INVALID_NUMBER
------------  --------------
1             0

This makes VALIDATE_CONVERSION() useful in filters:

SELECT raw_value
FROM staging_data
WHERE VALIDATE_CONVERSION(raw_value AS NUMBER) = 1;

Validate a Date with a Format Model

SELECT VALIDATE_CONVERSION(
  '2026-10-07' AS DATE,
  'YYYY-MM-DD'
) AS is_valid_date
FROM dual;

This returns 1.

A value such as '2026-99-99' returns 0 for the same format model.

Validate a Timestamp

SELECT VALIDATE_CONVERSION(
  '2026-10-07 21:30:00' AS TIMESTAMP,
  'YYYY-MM-DD HH24:MI:SS'
) AS is_valid_timestamp
FROM dual;

Validate an Interval

SELECT
  VALIDATE_CONVERSION(
    '3 04:05:06' AS INTERVAL DAY TO SECOND
  ) AS is_valid_interval
FROM dual;

NULL Behavior

A SQL NULL expression returns 1:

SELECT VALIDATE_CONVERSION(NULL AS NUMBER)
FROM dual;

That behavior means VALIDATE_CONVERSION() answers the question “would this value be convertible if present?” It does not treat NULL as invalid input.

If you need to reject nulls as well, test both conditions:

WHERE raw_value IS NOT NULL
  AND VALIDATE_CONVERSION(raw_value AS NUMBER) = 1

VALIDATE_CONVERSION vs DEFAULT ON CONVERSION ERROR

Use VALIDATE_CONVERSION() when you want a boolean-style 1 or 0 result.

For some conversion functions, Oracle also supports DEFAULT ... ON CONVERSION ERROR, which lets you return a fallback value instead of testing first.

Summary

  • VALIDATE_CONVERSION() checks convertibility without returning the converted value.
  • It returns 1 for a successful conversion and 0 for a conversion failure.
  • SQL NULL returns 1.
  • Format models and NLS parameters can be supplied for supported target types.
  • It is especially useful for staging tables and dirty imported text.

See Oracle’s VALIDATE_CONVERSION documentation.