Thursday, September 12, 2019

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


You may also want to see this article about the ORA-12899 which is returned if a value larger than column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what is defined for the NUMBER datatype column. Number datatype columns are defined in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”, it would mean that maximum width of the data could be 5 digits, out of which 2 will be decimal part (for example 123.45). Following are some examples that explain this concept further.

-- In the following example, the inserted value should only have 2 digits, and both digits should be in the decimal part of the number

SQL> create table test (sal number(2,2));

Table created.

SQL> insert into test values(2.3333);
insert into test values(2.3333)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(2.3);
insert into test values(2.3)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(2);
insert into test values(2)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(.2);

1 row created.

SQL>


-- In the following example, the inserted value should only have 2 digits for precision part only as no decimal could be inserted in this column, although INSERT statement would succeed if decimal value is mentioned.

SQL> create table test (sal number(2));

Table created.

SQL> insert into test values(2.1);

1 row created.

SQL> insert into test values(2.11);

1 row created.

SQL> insert into test values(2.111);

1 row created.

SQL> insert into test values(2.1110000);

1 row created.

SQL> insert into test values(22.1110000);

1 row created.

SQL> insert into test values(223.1110000);
insert into test values(223.1110000)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * from test;

       SAL
----------
         2
         2
         2
         2
        22



-- In the following example, maximum 6 digits could be inserted in the column out of which 4 have to be decimal part. As soon as we try to insert 3 digits in precision part, ORA-01438 would be returned because 4 places have to be reserved for the decimal part.

SQL> create table test (sal number(6,4));

Table created.

SQL> insert into test values(25.65743);

1 row created.

SQL> insert into test values(2534333.65743);
insert into test values(2534333.65743)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(253433.65743);
insert into test values(253433.65743)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(2534.65743);
insert into test values(2534.65743)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(2.65743);

1 row created.

SQL> insert into test values(22.65743);

1 row created.

SQL> insert into test values(223.65743);
insert into test values(223.65743)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into test values(243.5);
insert into test values(243.5)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * from test;

       SAL
----------
   25.6574
    2.6574
   22.6574


No comments:

Post a Comment

Popular Posts - All Times