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

Question

We get sometimes the following error from our partner s database    lt i gt ORA-01438  value larger than specified precision allows for this column lt  i gt    The full response looks like the following    lt  xml version  1 0  encoding  windows-1251   gt   lt response gt     lt status code gt  lt  status code gt     lt error text gt ORA-01438  value larger than specified precision allows for this column ORA-06512  at  amp quot UMAIN PAY NET V1 PKG amp quot   line 176 ORA-06512  at line 1 lt  error text gt     lt pay id gt 5592988 lt  pay id gt     lt time stamp gt  lt  time stamp gt   lt  response gt    What can be the cause for this error

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It might be a good practice to define variables like below   v departmentid departments department id TYPE    NOT like below   v departmentid NUMBER 4

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It might be a good practice to define variables like below   v departmentid departments department id TYPE    NOT like below   v departmentid NUMBER 4

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to plsql

Examples related to ora-01438