SQL 错误：ORA-12899：列的值太大
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4347888/
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
SQL Error: ORA-12899: value too large for column
提问by David
I have created the following table
我创建了下表
CREATE TABLE Customers(
CustomerID varchar2(9) PRIMARY KEY,
Customer_Contact varchar2(40) NOT NULL,
Address varchar2(20) NOT NULL,
Post_Code varchar2(7) NOT NULL,
Telephone_Number varchar2(11) NOT NULL)
And I am currently trying to use the INSERT VALUES statement. I have written the following statement
我目前正在尝试使用 INSERT VALUES 语句。我写了以下声明
INSERT INTO Customers VALUES(
501623129,
'John Petterson',
'-- Singleton Close London',
'--- ---', 02082860222)
When I try to run the statement it gives me the following error message.
当我尝试运行该语句时，它给了我以下错误消息。
Error starting at line 4 in command: INSERT INTO Customers VALUES(501623129, 'David Patterson', '30 Singleton Close London', 'SW17 9JY', 02082860642) Error report: SQL Error: ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 20) 12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"
从命令第 4 行开始出错：INSERT INTO Customers VALUES(501623129, 'David Patterson', '30 Singleton Close London', 'SW17 9JY', 02082860642) 错误报告：SQL 错误：ORA-12899：列的值太大“ DJ"."CUSTOMERS"."ADDRESS"（实际：25，最大值：20）12899.00000 - “列 %s 的值太大（实际：%s，最大值：%s）”
回答by Sathyajith Bhat
ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 2
Tells you what the error is. Address can hold maximum of 20 characters, you are passing 25 characters.
告诉你错误是什么。地址最多可容纳 20 个字符，您传递的是 25 个字符。
回答by wweicker
As mentioned, the error message shows you the exact problem: you are passing 25 characters into a field set up to hold 20. You might also want to consider defining the columns a little more precisely. You can define whether the VARCHAR2 column will store a certain number of bytes or characters. You may encounter a problem in the future where you try to insert a multi byte character into the field, for example this is 5 characters in length but it won't fit into 5 bytes: 'àèì??'
如前所述，错误消息显示了确切的问题：您将 25 个字符传递到设置为容纳 20 个字符的字段中。您可能还需要考虑更精确地定义列。您可以定义 VARCHAR2 列是否将存储一定数量的字节或字符。将来您可能会遇到问题，尝试在字段中插入多字节字符，例如，长度为 5 个字符，但无法放入 5 个字节：'àèì??'
Here is an example:
下面是一个例子：
CREATE TABLE Customers(CustomerID VARCHAR2(9 BYTE), ...
or
或者
CREATE TABLE Customers(CustomerID VARCHAR2(9 CHAR), ...
回答by LenLo
This answer still comes up high in the list for ORA-12899 and lot of non helpful comments above, even if they are old. The most helpful comment was #4 for any professional trying to find out why they are getting this when loading data.
这个答案仍然在 ORA-12899 的列表中名列前茅，并且上面有很多没有帮助的评论，即使它们已经很老了。最有用的评论是 #4 对于任何试图找出他们在加载数据时得到这个的专业人士。
Some characters are more than 1 byte in length, especially true on SQL Server. And what might fit in a varchar(20) in SQLServer won't fit into a similar varchar2(20) in Oracle.
某些字符的长度超过 1 个字节，在 SQL Server 上尤其如此。可能适合 SQLServer 中 varchar(20) 的内容不适合 Oracle 中类似的 varchar2(20)。
I ran across this error yesterday with SSIS loading an Oracle database with the Attunity drivers and thought I would save folks some time.
我昨天在 SSIS 加载带有 Attunity 驱动程序的 Oracle 数据库时遇到了这个错误，并认为我可以为人们节省一些时间。
回答by yu yang Jian
In my case I'm using C# OracleCommandwith OracleParameter, and I set all the the parameters Sizeproperty to max length of each column, then the error solved.
在我的情况下，我使用C# OracleCommandwith OracleParameter，并将所有参数Size属性设置为每列的最大长度，然后错误解决了。
OracleParameter parm1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Varchar2;
param1.Value = "test1";
param1.Size = 8;
OracleParameter parm2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Varchar2;
param2.Value = "test1";
param2.Size = 12;
回答by user6898571
example : 1 and 2 table is available
示例：1 和 2 表可用
1 table delete entry and select nor 2 table records and insert to no 1 table . when delete time no 1 table dont have second table records example emp id not available means this errors appeared
1 个表删除条目并选择或 2 个表记录并插入到第 1 个表。当删除时间没有 1 表没有第二个表记录示例 emp id 不可用意味着出现此错误
