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?

Answer 1

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 2

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 3

It is also possible to get this error code, if you are using PHP and bound integer variables (oci_bind_by_name with SQLT_INT). If you try to insert NULL via the bound variable, then you get this error or sometimes the value 2 is inserted (which is even more worse).

To solve this issue, you must bind the variable as string (SQLT_CHR) with fixed length instead. Before inserting NULL must be converted into an empty string (equals to NULL in Oracle) and all other integer values must be converted into its string representation.

Answer 4

FYI: Numeric field size violations will give ORA-01438: value larger than specified precision allowed for this column

VARCHAR2 field length violations will give ORA-12899: value too large for column...

Oracle makes a distinction between the data types of the column based on the error code and message.

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

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.

Answer 7

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 8

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 9

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]