ORA-01438: value larger than specified precision allowed for this column

ORA-01438: value 1000 greater than specified precision (5, 2) for column

The number has more digits before the decimal point than the NUMBER(p,s) column allows.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15

NUMBER(p,s) stores at most p digits in total, s of them after the decimal point. So NUMBER(5,2) has room for only 3 digits before the point, and its largest value is 999.99. A number with more integer digits is rejected with ORA-01438.

23ai shows the value and the precision: ORA-01438: value 1000 greater than specified precision (5, 2) for column. In this version (23.26) the column name was blank in all three languages, but the * in the output points at the value. Check the column's size in user_tab_columns (data_precision, data_scale).

Causes and fixes

  1. The value has too many digits for NUMBER(p,s)
  2. An UPDATE calculation pushes a value over the limit
  3. A sequence grew past the size of a NUMBER(3) ID column

1The value has too many digits for NUMBER(p,s)

price NUMBER(5,2) accepted 999.99, but INSERT ... VALUES (2, 1000) failed with value 1000 greater than specified precision (5, 2). user_tab_columns showed precision 5, scale 2: 5 - 2 = 3 integer digits, and 1000 has 4.

Widening the column with ALTER TABLE item MODIFY (price NUMBER(9,2)) let the same INSERT succeed.

Setup
Setuprun as lab_a
CREATE TABLE item (item_id NUMBER PRIMARY KEY, price NUMBER(5,2));
INSERT INTO item VALUES (1, 999.99);
COMMIT;
OUTPUT
Table created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO item VALUES (2, 1000);
OUTPUT
INSERT INTO item VALUES (2, 1000)
                            *
ERROR at line 1:
ORA-01438: value 1000 greater than specified precision (5, 2) for column
Help: https://docs.oracle.com/error-help/db/ora-01438/
Checkrun as lab_a
SELECT column_name, data_precision, data_scale FROM user_tab_columns WHERE table_name = 'ITEM' ORDER BY column_id;
OUTPUT
COLUMN_NAME                    DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
ITEM_ID
PRICE                                       5          2

2 rows selected.
Fixrun as lab_a
ALTER TABLE item MODIFY (price NUMBER(9,2));
OUTPUT
Table altered.

Widening a NUMBER column worked here with a row already in the table.

After the fixrun as lab_a
INSERT INTO item VALUES (2, 1000);
OUTPUT
1 row created.

2An UPDATE calculation pushes a value over the limit

UPDATE item SET price = price * 1.2 failed with value 1020. Only one row was the problem: 850 * 1.2 = 1020 (500 and 120.5 fit). Because the statement failed, none of the 3 rows were changed.

Running the calculation as a SELECT first (WHERE price * 1.2 >= 1000) found item 2. After widening the column to NUMBER(9,2), the UPDATE changed all 3 rows: 600, 1020, 144.6.

Setup
Setuprun as lab_a
CREATE TABLE item (item_id NUMBER PRIMARY KEY, price NUMBER(5,2));
INSERT INTO item VALUES (1, 500);
INSERT INTO item VALUES (2, 850);
INSERT INTO item VALUES (3, 120.5);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
UPDATE item SET price = price * 1.2;
OUTPUT
UPDATE item SET price = price * 1.2
                              *
ERROR at line 1:
ORA-01438: value 1020 greater than specified precision (5, 2) for column
Help: https://docs.oracle.com/error-help/db/ora-01438/
Checkrun as lab_a
SELECT item_id, price, price * 1.2 AS new_price FROM item WHERE price * 1.2 >= 1000;
OUTPUT
   ITEM_ID      PRICE  NEW_PRICE
---------- ---------- ----------
         2        850       1020

1 row selected.
Fixrun as lab_a
ALTER TABLE item MODIFY (price NUMBER(9,2));
OUTPUT
Table altered.
After the fixrun as lab_a
UPDATE item SET price = price * 1.2;
SELECT item_id, price FROM item ORDER BY item_id;
COMMIT;
OUTPUT
3 rows updated.


   ITEM_ID      PRICE
---------- ----------
         1        600
         2       1020
         3      144.6

3 rows selected.


Commit complete.

3A sequence grew past the size of a NUMBER(3) ID column

ticket_id NUMBER(3) was filled from ticket_seq. IDs 998 and 999 went in, and the next one, 1000, failed with precision (3, 0). The current max was 999, so the column was simply full.

After ALTER TABLE ticket MODIFY (ticket_id NUMBER(10)), the INSERT worked. The new row got 1004, not 1000: a failed INSERT still uses up the sequence number it fetched.

Setup
Setuprun as lab_a
CREATE TABLE ticket (ticket_id NUMBER(3) PRIMARY KEY, title VARCHAR2(20));
CREATE SEQUENCE ticket_seq START WITH 998;
INSERT INTO ticket VALUES (ticket_seq.NEXTVAL, 'a');
INSERT INTO ticket VALUES (ticket_seq.NEXTVAL, 'b');
COMMIT;
OUTPUT
Table created.


Sequence created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO ticket VALUES (ticket_seq.NEXTVAL, 'c');
OUTPUT
INSERT INTO ticket VALUES (ticket_seq.NEXTVAL, 'c')
                           *
ERROR at line 1:
ORA-01438: value 1000 greater than specified precision (3, 0) for column
Help: https://docs.oracle.com/error-help/db/ora-01438/
Checkrun as lab_a
SELECT MAX(ticket_id) AS max_id FROM ticket;
SELECT data_precision FROM user_tab_columns WHERE table_name = 'TICKET' AND column_name = 'TICKET_ID';
OUTPUT
    MAX_ID
----------
       999

1 row selected.


DATA_PRECISION
--------------
             3

1 row selected.
Fixrun as lab_a
ALTER TABLE ticket MODIFY (ticket_id NUMBER(10));
OUTPUT
Table altered.

Size ID columns for the long run. Gaps in sequence numbers after failures are normal.

After the fixrun as lab_a
INSERT INTO ticket VALUES (ticket_seq.NEXTVAL, 'd');
SELECT ticket_id, title FROM ticket ORDER BY ticket_id;
COMMIT;
OUTPUT
1 row created.


 TICKET_ID TITLE
---------- --------------------
       998 a
       999 b
      1004 d

3 rows selected.


Commit complete.

FAQ

What does ORA-01438 value larger than specified precision mean?

The value has more digits before the decimal point than the column allows. For NUMBER(p,s) that limit is p - s digits, so NUMBER(5,2) goes up to 999.99.

How do I fix ORA-01438?

Check data_precision and data_scale in user_tab_columns, then either fix the value or widen the column, for example ALTER TABLE item MODIFY (price NUMBER(9,2)).

How do I find which column or row causes ORA-01438?

23ai puts the value and precision in the message, e.g. value 1020 ... (5, 2). Compare it with the columns' sizes, and for an UPDATE run the same calculation as a SELECT to find the rows that exceed the limit.

Why does NUMBER(5,2) not accept 1000?

5 is the total number of digits and 2 of them are after the decimal point, leaving only 3 before it. 1000 needs 4.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors