我如何知道 10385274000 是否适合：NUMBER(10) for Oracle？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1215535/
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
How can I know if 10385274000 fits into: NUMBER(10) for Oracle?
提问by OscarRyz
I been working the whole week to troubleshot a production error.
我整个星期都在努力解决生产错误。
I have eventually got the the point where I can find the culprit record which is causing all the mess.
我终于明白了，我可以找到导致所有混乱的罪魁祸首记录。
I've got the following error message:
我收到以下错误消息：
java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-01438: value larger than specified precision allows for this column
Eventuall from all the info I think this might be the wrong data, the system is trying to insert:
最终从所有信息中我认为这可能是错误的数据，系统正在尝试插入：
10385274000
Into a NUMBER(10)
成数(10)
How can I know if that value fits or no?
我怎么知道这个值是否合适？
Thank you
谢谢
EDIT
编辑
As per Michel Todd suggestion:
根据 Michel Todd 的建议：
create table xyz( testfield number( 10 ) );
insert into xyz values( 10385274000 )
Error: ORA-01438: value larger than specified precision allowed for this column
Thank you guys!!!
谢谢你们！！！
Thank you stackoverflow
谢谢你
EDIT
编辑
Notes to my self ( not to forget what was the problem )
给我自己的笔记（不要忘记问题出在哪里）
I had this Oracle product which stores in a database table the time of an event
我有这个 Oracle 产品，它将事件发生的时间存储在数据库表中
START_TIME|END_TIME
It turns out everynight it backups this information into another table but performs a trnsformation in the process. It does store as:
事实证明，它每天晚上都会将此信息备份到另一个表中，但会在此过程中执行转换。它确实存储为：
TOTALTIME
The problem comes when this field is calculated by subtracting ENDTIME - STARTTIME. The resulting number is stored in this column which is defined as: NUMBER(10)
当这个字段是通过减去 ENDTIME - STARTTIME 来计算时，问题就出现了。结果数字存储在此列中，定义为：NUMBER(10)
Well, it turns out if END_TIME-START_TIME are too far away in the time ( about 4 months or so ) the value ( in milliseconds ) would be SO big it won't fit in the target column ( I guess it has something like endTime.getTime() - startTime.getTime() inside the code )
好吧，事实证明，如果 END_TIME-START_TIME 在时间（大约 4 个月左右）内太远，则值（以毫秒为单位）会太大以至于不适合目标列（我猜它有像 endTime 这样的东西） .getTime() - 代码中的 startTime.getTime() )
All this sounds too easy and too silly now, but it took me 4 day+ to find out, because since this is a closed application I didn't have a clue of what was happening, the only thing I've got was the stacktrace.
所有这些现在听起来太简单也太愚蠢了，但我花了 4 天多的时间才发现，因为这是一个封闭的应用程序，我不知道发生了什么，我唯一得到的就是堆栈跟踪。
I had to reverse engineer ( in the OLD sense of the word, by hand and obviously with out the source) the entire process to find this out.
我不得不对整个过程进行逆向工程（在这个词的旧意义上，手工并且显然没有来源）才能找到这一点。
When I did it, I've got the same error in my "hand coded migrator" and find out how to solve it!
当我这样做时，我在我的“手工编码迁移器”中遇到了同样的错误，并找出如何解决它！
回答by Emil H
The number 10 in NUMBER(10) specifies the field size. That means that the field can hold a number up to 10 characters long. Your number has 11 digits and thus the value is to large to fit. Anything smaller than (<) 10 billion (10 000 000 000) can be inserted without trouble. That's what you need to check for if you want to validate the value before inserting.
NUMBER(10) 中的数字 10 指定字段大小。这意味着该字段最多可以容纳 10 个字符的数字。您的号码有 11 位数字，因此该值太大而无法容纳。任何小于 (<) 100 亿 (10 000 000 000) 的东西都可以轻松插入。如果您想在插入之前验证值，这就是您需要检查的内容。
