"Ora-01438: Value Larger Than Specified Precision Allowed for This Column" When Inserting 3

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

You can't update with a number greater than 1 for datatype number(2,2) is because, the first parameter is the total number of digits in the number and the second one (.i.e 2 here) is the number of digits in decimal part. I guess you can insert or update data < 1. i.e. 0.12, 0.95 etc.

Please check NUMBER DATATYPE in NUMBER Datatype.

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

I believe the error is actually being caused by the way you declared the NUMBER type for certain of your columns:

NUMBER(3,4)

This is defining a number with a precision of 3 significant figures, with 4 of them occurring after the decimal place and -1 of them occurring before the decimal place. Read this last sentence again carefully until you see why it doesn't work for the value 1. (It would work OK if you tried to insert the value 0.002 though... up to four decimal places, and the first has to be zero.)

If you want to give 4 decimal places of precision to your latitude and longitude values, then use the following defintion:

NUMBER(7, 4)

This means 3 digits before the decimal places and 4 digits after the decimal place.

how to make sure to never get ora-01438: value larger than specified precision allowed for this column?

NUMBER(10, 10) means 10 digits and a scale of 10.

That means you have 10 digits right of the decimal point which means no digit left of it.

So having the table

CREATE TABLE t
(
   test   NUMBER (10, 10)
);

insert into t values (0.9999999999); will work, while

insert into t values (0.99999999999);will fail because the value is rounded up to 1.

So if num/denom is 1 or even larger you will get ORA-01438: value larger than specified precision allowed for this column.

But you will also get this error, if num/denom is larger then 0.99999999995 as oracle tries to round it to 1.

---

Related Topics