Ways to resolve the error - ora 01438 value larger than specified precision allowed for this column.

Question

I get the “ora 01438 value larger than specified precision allowed for this column” error as the title says. How can I fix it so the error goes away? Here is my detail:

UPDATE
  PROG_OWN.PROG_TPORCENTAJE_MERMA
 SET
  PCT_MERMA = 3
 WHERE
  IDN_PORCENTAJE_MERMA = 1

COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
 PCT_MERMA 3 NUMBER 2 0 2

When I operated it, I received the error text:

*** Start Block *** 
[Error Code: 1438, SQL State: 22003] ORA-01438: value larger than specified precision allowed for this column

I appreciate any help from you.

lyytutoria · Accepted Answer

The cause:

This error happens because for datatype number (2,2), you cannot update with a number higher than 1. The reason is that the first parameter (in this case is 2) is the number of digits in the decimal component and the first parameter is the total number of digits in the number.

Solution:

You only can update numbers which are < 1, for example: 0.12, 0.95, 0.85,....

Let's read this NUMBER Datatype to check number databse.

Paul Armand · Answer

NUMBER (precision, scale) refers to precision total digits. scale digits are right at the decimal point.

NUMBER(2,2), in other words, means a number that has 2 digits. Both of these are decimals. It is possible to mean NUMBER(4,2) to get four digits. Of these, 2 are decimals. You can only insert values with a null integer part at the moment.

Get more information at the Oracle Docs.

Sign Up

Sign In

Forgot Password

ITtutoria Latest Questions

Ways to resolve the error – ora 01438 value larger than specified precision allowed for this column.