SQL ORA-01438: 大于指定精度的值允许此列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/181344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01438: value larger than specified precision allows for this column
提问by user11104
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 "UMAIN.PAY_NET_V1_PKG", 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?
导致此错误的原因是什么？
回答by Gary Myers
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
回答by Thorsten
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).
错误似乎不是字符字段之一，而是更多的数字字段。（如果这是一个像 WW 提到的字符串问题，你会得到一个“值太大”或类似的东西。）可能你使用的数字比允许的多，例如 1,000000001 在定义为数字 (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.
查看 WW 提到的源代码以找出可能导致问题的列。如果可能，然后检查那里正在使用的数据。
回答by WW.
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.
这表明您试图将太大的内容放入列中。例如，您有一个 VARCHAR2(10) 列并且您要输入 11 个字符。号码也是一样。
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.
这发生在包 UMAIN 的第 176 行。你需要去看看它，看看它在做什么。希望您可以在源代码管理（或从 user_source）中查找它。更高版本的 Oracle 会更好地报告此错误，告诉您哪个列和什么值。
回答by cagcowboy
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)
除了前面的答案，您应该注意到定义为 VARCHARS(10) 的列将存储 10个字节，而不是 10 个字符，除非您将其定义为 VARCHAR2(10 CHAR)
[The OP's question seems to be number related... this is just in case anyone else has a similar issue]
[OP 的问题似乎与数字有关......以防万一其他人有类似的问题]
回答by warren
From http://ora-01438.ora-code.com/(the definitive resource outside of Oracle Support):
来自http://ora-01438.ora-code.com/（Oracle支持之外的权威资源）：
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.
ORA-01438：值大于此列允许的指定精度
原因：在插入或更新记录时，输入的数值超出了为该列定义的精度。
操作：输入符合数字列精度的值，或使用 MODIFY 选项和 ALTER TABLE 命令来扩展精度。
http://ora-06512.ora-code.com/:
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.
ORA-06512：在 stringline 字符串
原因：当堆栈被未处理的异常展开时，回溯消息。
行动：修复导致异常的问题或为这种情况编写异常处理程序。或者您可能需要联系您的应用程序管理员或 DBA。
回答by Robert Gould
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
我遇到的一个非常棘手的问题是，根据 Oracle 版本，描述列属性的 OCI 调用的行为不同。描述在 9i、10g 和 11g 上创建的没有任何 prec 或 scale 的简单 NUMBER 列返回差异
回答by Priyome
FYI: Numeric field size violations will give ORA-01438: value larger than specified precision allowed for this column
仅供参考：数字字段大小违规将导致 ORA-01438：值大于此列允许的指定精度
VARCHAR2 field length violations will give ORA-12899: value too large for column...
VARCHAR2 字段长度违规将导致 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.
Oracle 根据错误代码和消息来区分列的数据类型。
回答by gokhant
It might be a good practice to define variables like below:
定义如下变量可能是一个好习惯：
v_departmentid departments.department_id%TYPE;
NOT like below:
不喜欢下面：
v_departmentid NUMBER(4)
