What causes ORA-01426?

Multiplication or exponentiation of large numbers in a pipeline transformation. Aggregation function (SUM, PRODUCT) over a dataset with extreme outliers. Target column defined with precision/scale too narrow for the actual data range. Incorrect unit conversion (e.g. bytes to terabytes without scaling)

How do I fix ORA-01426?

Step 1: Identify the calculation causing the overflow and review the expected data range.. Step 2: Add range validation: `WHERE source_value BETWEEN -1e18 AND 1e18`.. Step 3: Use TO_CHAR to inspect the intermediate values before overflow: `SELECT TO_CHAR(col) FROM source`.. Step 4: Widen the NUMBER column precision: `ALTER TABLE t MODIFY col NUMBER(38)` (Oracle supports up to 38 digits).. Step 5: Use CASE WHEN to handle outliers: `CASE WHEN value > 1e18 THEN NULL ELSE value END`.

Medium severitydata

Oracle Database Error:
ORA-01426

What does this error mean?

An arithmetic operation produced a result that exceeds the maximum value that Oracle can store in a NUMBER data type.

Common causes

1Multiplication or exponentiation of large numbers in a pipeline transformation
2Aggregation function (SUM, PRODUCT) over a dataset with extreme outliers
3Target column defined with precision/scale too narrow for the actual data range
4Incorrect unit conversion (e.g. bytes to terabytes without scaling)

How to fix it

1Step 1: Identify the calculation causing the overflow and review the expected data range.
2Step 2: Add range validation: `WHERE source_value BETWEEN -1e18 AND 1e18`.
3Step 3: Use TO_CHAR to inspect the intermediate values before overflow: `SELECT TO_CHAR(col) FROM source`.
4Step 4: Widen the NUMBER column precision: `ALTER TABLE t MODIFY col NUMBER(38)` (Oracle supports up to 38 digits).
5Step 5: Use CASE WHEN to handle outliers: `CASE WHEN value > 1e18 THEN NULL ELSE value END`.

Example log output

ORA-01426: numeric overflow
SQL: UPDATE metrics SET total_bytes = source_bytes * 1073741824 WHERE id = :1

Frequently asked questions

What is the maximum value Oracle NUMBER can hold?

Oracle NUMBER supports up to 38 significant digits (approximately 10^125 for positive values). Overflow typically indicates incorrect scale/precision in the column definition or a data quality issue.

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