What causes ORA-01722?

Source string column contains non-numeric values (empty string, 'N/A', currency symbols) being cast to NUMBER. TO_NUMBER conversion without format model fails on locale-specific formatting (commas as decimal separator). ADF type mapping converts a string column to a numeric type without validation. NULL or empty string passed to a function that expects a number

How do I fix ORA-01722?

Step 1: Use REGEXP_LIKE to filter invalid values before conversion: `WHERE REGEXP_LIKE(col, '^[0-9]+(\.[0-9]+)?$')`.. Step 2: Use CASE + TO_NUMBER with exception handling: `CASE WHEN REGEXP_LIKE(col, '^-?[0-9.]+$') THEN TO_NUMBER(col) ELSE NULL END`.. Step 3: Specify a format model in TO_NUMBER for locale-specific formatting: `TO_NUMBER(col, '999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')`.. Step 4: Fix the source data to ensure numeric columns only contain numeric values.. Step 5: Add a data quality validation step before the Oracle load.

Medium severitydata

Oracle Database Error:
ORA-01722

What does this error mean?

Oracle attempted to convert a string to a number but the string contains non-numeric characters.

Common causes

1Source string column contains non-numeric values (empty string, 'N/A', currency symbols) being cast to NUMBER
2TO_NUMBER conversion without format model fails on locale-specific formatting (commas as decimal separator)
3ADF type mapping converts a string column to a numeric type without validation
4NULL or empty string passed to a function that expects a number

How to fix it

1Step 1: Use REGEXP_LIKE to filter invalid values before conversion: `WHERE REGEXP_LIKE(col, '^[0-9]+(\.[0-9]+)?$')`.
2Step 2: Use CASE + TO_NUMBER with exception handling: `CASE WHEN REGEXP_LIKE(col, '^-?[0-9.]+$') THEN TO_NUMBER(col) ELSE NULL END`.
3Step 3: Specify a format model in TO_NUMBER for locale-specific formatting: `TO_NUMBER(col, '999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')`.
4Step 4: Fix the source data to ensure numeric columns only contain numeric values.
5Step 5: Add a data quality validation step before the Oracle load.

Example log output

ORA-01722: invalid number
SQL: INSERT INTO metrics (amount) VALUES (TO_NUMBER('€1,234.56'))

Frequently asked questions

How do I find which rows have invalid numbers?

Run `SELECT col FROM table WHERE NOT REGEXP_LIKE(col, '^-?[0-9]+(\.[0-9]+)?$') AND col IS NOT NULL;` on the source to identify the bad values before loading.

Source · docs.oracle.com/error-help/db/ora-01722