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.