Oracle Error ORA-01438

Error code

ORA-01438

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Data Types & Conversion

Last reviewed

24 October 2026

What it means

ORA-01438 means an INSERT or UPDATE tried to store a number with more digits before the decimal point than the column's NUMBER(precision, scale) definition allows.

A column declared NUMBER(p, s) holds at most p significant digits, s of them after the decimal point, so at most p minus s digits before it. Oracle's current reference gives the example of NUMBER(3,2): 3.89 fits, but 123.89 raises the error because only one digit is allowed to the left of the decimal point. Extra digits after the decimal point are not an error: the SQL Language Reference shows that they are rounded to the scale.

Current releases name the value, the precision and scale, and the column; 19c and 21c print a generic sentence and leave you to find the column. In PL/SQL, assigning a too-large value to a constrained NUMBER variable raises ORA-06502 instead; ORA-01438 comes from storing the value in a table column.

Common causes

• A value is larger than the column was designed for: a price above 999.99 in NUMBER(5,2), or an id past 999 in NUMBER(3).
• A unit change in the source, such as amounts sent in cents instead of currency units, multiplies every value by 100.
• A calculated value (a total, a rate times a quantity, a percentage stored as 1234 instead of 12.34) is written to a column sized for its inputs.
• A sequence feeding a narrow NUMBER(n) key column has grown past 10 to the power n minus 1.
• A load maps source columns to the wrong target columns, so a large number lands in a small field.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT column_name, data_type, data_precision, data_scale,
       data_precision - data_scale AS digits_before_point
FROM all_tab_columns
WHERE owner = 'SHOP'
  AND table_name = 'ORDER_LINES'
  AND data_type = 'NUMBER'
ORDER BY column_id;

SELECT line_id, unit_price
FROM shop.order_lines_staging
WHERE ABS(unit_price) >= 1000   -- 10 to the power (precision - scale)
ORDER BY ABS(unit_price) DESC;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

ALTER TABLE shop.order_lines MODIFY (unit_price NUMBER(9, 2));

INSERT INTO shop.order_lines (line_id, order_id, unit_price)
SELECT line_id, order_id, ROUND(unit_price_cents / 100, 2)
FROM shop.order_lines_staging;

INSERT INTO shop.order_lines (line_id, order_id, unit_price)
SELECT line_id, order_id, unit_price
FROM shop.order_lines_staging
WHERE ABS(unit_price) < 1000;

Examples

A price too large for NUMBER(5,2)

Not reproduced here: no Oracle Database is installed for this site. The result follows the message format in Oracle's current error reference, with this example's value and column filled in; how the precision and the column name are printed is illustrative. Oracle Database 19c and 21c print "ORA-01438: value larger than specified precision allowed for this column".

CREATE TABLE order_lines (
    line_id    NUMBER PRIMARY KEY,
    unit_price NUMBER(5, 2)
);

INSERT INTO order_lines (line_id, unit_price) VALUES (1, 999.99);  -- fits
INSERT INTO order_lines (line_id, unit_price) VALUES (2, 1250.5);  -- 4 digits before the point

ORA-01438: value 1250.5 greater than specified precision (5, 2) for column "SHOP"."ORDER_LINES"."UNIT_PRICE"

Extra decimals are rounded, extra whole digits are not

Not reproduced here: no Oracle Database is installed for this site. The rounding of extra decimals is described in the NUMBER section of Oracle's SQL Language Reference; the error format comes from Oracle's error reference and the printed names are illustrative. 12345 needs five digits before the point, and NUMBER(5,2) allows three.

INSERT INTO order_lines (line_id, unit_price) VALUES (3, 12.3456);  -- stored as 12.35
INSERT INTO order_lines (line_id, unit_price) VALUES (4, 12345);    -- too many digits

ORA-01438: value 12345 greater than specified precision (5, 2) for column "SHOP"."ORDER_LINES"."UNIT_PRICE"

How to prevent it

Size NUMBER columns for the largest legitimate value plus headroom, and remember that precision counts digits on both sides of the point. For surrogate keys fed by sequences or identity columns, use plain NUMBER or a generous precision.

Validate units and ranges in the load (or with a check constraint) so an upstream change shows up as clear rejected rows. Declare PL/SQL variables with %TYPE so they follow the column definition. See SQL data types explained.

Common questions

Learn more on CodeWithSQL

• SQL data types explained

Official documentation

• ORA-01438 (Oracle Database Error Messages)
• Data Types, including NUMBER precision and scale (Oracle SQL Language Reference 19c)
• ALTER TABLE (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.