VALIDATE_CONVERSION

📖 Описание

Функция Oracle/PLSQL VALIDATE_CONVERSION проверяет, можно ли преобразовать значение к указанному типу данных: возвращает 1 при допустимом преобразовании, включая NULL, и 0 при ошибке преобразования. Ошибки вычисления самого выражения она не перехватывает.

Функция доступна начиная с Oracle 12c Release 2 (12.2). Она удобна при загрузке данных из текстовых файлов и внешних таблиц, когда в строковых столбцах могут встретиться некорректные числа и даты.


🧩 Синтаксис

VALIDATE_CONVERSION(expr AS type_name [, format_mask [, nls_param]])

⚙️ Параметры и аргументы

expr — проверяемое выражение. NULL даёт 1: это проверка допустимости преобразования, а не обязательности заполнения.

type_name — NUMBER, BINARY_FLOAT, BINARY_DOUBLE, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND или INTERVAL YEAR TO MONTH. Тип указывается без кавычек.

format_mask и nls_param задают формат и параметры преобразования, как у TO_NUMBER, TO_DATE и TO_TIMESTAMP; для двух типов INTERVAL эти аргументы не применяются.

VALIDATE_CONVERSION возвращает числовой признак, а не преобразованное значение. Без явной маски и NLS-параметров проверка может зависеть от настроек сеанса.


💻 Примеры

Создадим учебную таблицу строк из файла доставок. Примеры дат рассчитаны для григорианского календаря:

CREATE TABLE delivery_import (
  row_id NUMBER,
  fee_text VARCHAR2(20),
  shipped_text VARCHAR2(20)
);

INSERT INTO delivery_import VALUES (1, '240',  '09.04.2026');
INSERT INTO delivery_import VALUES (2, '63x',  '30.02.2026');
INSERT INTO delivery_import VALUES (3, '5800', '22.10.2026');
INSERT INTO delivery_import VALUES (4, NULL,   '2026-06-07');

Проверка чисел

SELECT row_id, fee_text,
       VALIDATE_CONVERSION(fee_text AS NUMBER) AS is_valid
FROM delivery_import
ORDER BY row_id;
ROW_ID FEE_TEXT IS_VALID
1 240 1
2 63x 0
3 5800 1
4 NULL 1

Для строки 4 результат равен 1: NULL можно преобразовать в NUMBER, получив NULL.

Проверка дат

SELECT row_id, shipped_text,
       VALIDATE_CONVERSION(shipped_text AS DATE, 'FXDD.MM.YYYY') AS is_valid
FROM delivery_import
ORDER BY row_id;
ROW_ID SHIPPED_TEXT IS_VALID
1 09.04.2026 1
2 30.02.2026 0
3 22.10.2026 1
4 2026-06-07 0

30 февраля не существует. Модификатор FX требует точного соответствия маске, поэтому строка с порядком год-месяц-день тоже не подходит.

Найдём некорректные числа:

SELECT row_id, fee_text
FROM delivery_import
WHERE VALIDATE_CONVERSION(fee_text AS NUMBER) = 0
ORDER BY row_id;
-- Строка: 2, 63x

Параметры NLS

Явно укажем запятую как десятичный разделитель:

SELECT VALIDATE_CONVERSION('2456,78' AS NUMBER,
       '9999D99', 'NLS_NUMERIC_CHARACTERS='',.''') FROM dual;
-- Результат: 1

Проверка TIMESTAMP

SELECT VALIDATE_CONVERSION('2026-06-07 16:45:20' AS TIMESTAMP,
       'FXYYYY-MM-DD HH24:MI:SS') FROM dual;
-- Результат: 1

Преобразование с DEFAULT ON CONVERSION ERROR

Начиная с Oracle 12.2, CAST, TO_NUMBER и TO_DATE могут возвращать заданное значение при ошибке преобразования. Для строк с числами:

SELECT row_id,
       CAST(fee_text AS NUMBER DEFAULT '-1' ON CONVERSION ERROR) AS fee
FROM delivery_import
ORDER BY row_id;
ROW_ID FEE
1 240
2 -1
3 5800
4 NULL

NULL остаётся NULL, потому что это не ошибка преобразования. DEFAULT не перехватывает ошибку вычисления исходного выражения; само значение по умолчанию тоже должно преобразовываться в целевой тип.

Не рассчитывайте на порядок вычисления отдельного WHERE VALIDATE_CONVERSION(...) = 1 и вызова TO_NUMBER в том же запросе. Встроенный DEFAULT ... ON CONVERSION ERROR позволяет обработать ошибку в самом преобразовании.

Как заменить VALIDATE_CONVERSION в старых версиях

Для Oracle 12.1 и более ранних версий можно создать учебную функцию, перехватывающую ошибки преобразования числа:

CREATE OR REPLACE FUNCTION demo_number_valid(p_text IN VARCHAR2)
  RETURN NUMBER
IS
  l_value NUMBER;
BEGIN
  l_value := TO_NUMBER(p_text);
  RETURN 1;
EXCEPTION
  WHEN VALUE_ERROR OR INVALID_NUMBER THEN
    RETURN 0;
END demo_number_valid;
/

demo_number_valid(NULL) возвращает 1: присваивание NULL проходит успешно, затем выполняется RETURN 1. Как и проверка без маски, эта функция использует NLS-настройки сеанса; она является примером только для чисел, а для дат нужны отдельная маска и обработка ошибок даты.

См. документацию Oracle 19: VALIDATE_CONVERSION.

См. документацию Oracle 12.2: VALIDATE_CONVERSION.

См. документацию Oracle 12.2: CAST.