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

Question

We get sometimes the following error from our partner's database:

<i>ORA-01438: value larger than specified precision allows for this column</i>

The full response looks like the following:

<?xml version="1.0" encoding="windows-1251"?>
<response>
  <status_code></status_code>
  <error_text>ORA-01438: value larger than specified precision allows for this column ORA-06512: at &quot;UMAIN.PAY_NET_V1_PKG&quot;, line 176 ORA-06512: at line 1</error_text>
  <pay_id>5592988</pay_id>
  <time_stamp></time_stamp>
</response>

What can be the cause for this error?

Thank you in advance.

Answer 1

This indicates you are trying to put something too big into a column. For example, you have a VARCHAR2(10) column and you are putting in 11 characters. Same thing with number.

This is happening at line 176 of package UMAIN. You would need to go and have a look at that to see what it is up to. Hopefully you can look it up in your source control (or from user_source). Later versions of Oracle report this error better, telling you which column and what value.

Not sure why this is tagge XML?

Answer 2

The error seems not to be one of a character field, but more of a numeric one. (If it were a string problem like WW mentioned, you'd get a 'value too big' or something similar.) Probably you are using more digits than are allowed, e.g. 1,000000001 in a column defined as number (10,2).

Look at the source code as WW mentioned to figure out what column may be causing the problem. Then check the data if possible that is being used there.

Answer 3

One issue I've had, and it was horribly tricky, was that the OCI call to describe a column attributes behaves diffrently depending on Oracle versions. Describing a simple NUMBER column created without any prec or scale returns differenlty on 9i, 1Og and 11g

Answer 4

Further to previous answers, you should note that a column defined as VARCHARS(10) will store 10 bytes, not 10 characters unless you define it as VARCHAR2(10 CHAR)

[The OP's question seems to be number related... this is just in case anyone else has a similar issue]

Answer 5

From http://ora-01438.ora-code.com/ (the definitive resource outside of Oracle Support):

ORA-01438: value larger than specified precision allowed for this column
Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
Action: Enter a value that complies with the numeric column's precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

http://ora-06512.ora-code.com/:

ORA-06512: at stringline string
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.

Answer 6

The number you are trying to store is too big for the field. Look at the SCALE and PRECISION. The difference between the two is the number of digits ahead of the decimal place that you can store.

select cast (10 as number(1,2)) from dual
             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

select cast (15.33 as number(3,2)) from dual
             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Anything at the lower end gets truncated (silently)

select cast (5.33333333 as number(3,2)) from dual;
CAST(5.33333333ASNUMBER(3,2))
-----------------------------
                         5.33

Answer 7

It might be a good practice to define variables like below:

v_departmentid departments.department_id%TYPE;

NOT like below:

v_departmentid NUMBER(4)

Answer 8

create table as1(id number(10,11))

but we cant insert the data into the table.

it will give error SQL> create table as1(id number(10,11)) 2 ;

Table created.

SQL> SQL> insert into as1 (id) values(45.1654); insert into as1 (id) values(45.1654) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column

BUT following is success

SQL> insert into as1 (id) values(0.01234567891);

1 row created.

Why so....? can any body please explain....