oracle ORA-01461: 只能为插入到 LONG 列而绑定 LONG 值-查询时发生
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9156019/
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-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying
提问by Rupesh
When I try to query objects, I end up with following error:
当我尝试查询对象时，最终出现以下错误：
ORA-01461: can bind a LONG value only for insert into a LONG column
Could someone please help me on the cause and solution of the problem?
有人可以帮助我解决问题的原因和解决方案吗？
采纳答案by Mark J. Bobak
Ok, well, since you didn't show any code, I'll make a few assumptions here.
好吧，既然你没有展示任何代码，我会在这里做一些假设。
Based on the ORA-1461 error, it seems that you've specified a LONG datatype in a select statement? And you're trying to bind it to an output variable? Is that right? The error is pretty straight forward. You can only bind a LONG value for insert into LONG column.
基于 ORA-1461 错误，您似乎在 select 语句中指定了 LONG 数据类型？你试图将它绑定到一个输出变量？那正确吗？错误很直接。您只能绑定一个 LONG 值以插入 LONG 列。
Not sure what else to say. The error is fairly self-explanatory.
不知道还有什么要说的。该错误是不言自明的。
In general, it's a good idea to move away from LONG datatype to a CLOB. CLOBs are much better supported, and LONG datatypes really are only there for backward compatibility.
一般来说，从 LONG 数据类型转移到 CLOB 是个好主意。CLOB 得到了更好的支持，而 LONG 数据类型实际上只是为了向后兼容。
Here's a list of LONG datatype restrictions
Hope that helps.
希望有帮助。
回答by Kiran
It can also happen with varchar2 columns. This is pretty reproducible with PreparedStatements through JDBC by simply
它也可能发生在 varchar2 列上。这很容易通过 JDBC 通过 PreparedStatements 重现
- creating a table with a column of varchar2 (20 or any arbitrary length) and
- inserting into the above table with a row containing more than 20 characters
- 创建一个包含 varchar2 列（20 或任意长度）的表和
- 在上表中插入超过 20 个字符的行
So as above said it can be wrong with types, or column width exceeded.
因此，如上所述，类型可能是错误的，或者超出了列宽。
Also note that as varchar2 allows 4k chars max, the real limit will be 2k for double byte chars
另请注意，由于 varchar2 最多允许 4k 个字符，因此双字节字符的实际限制为 2k
Hope this helps
希望这可以帮助
回答by Tomasz ?uk
This error occurs when one attempts to use a varchar variable longer than 4000 bytes in an SQL statement. PL/SQL allows varchars up to 32767 bytes, but the limit for database tables and SQL language is 4000. You can't use PL/SQL variables that SQL doesn't recognize in SQL statements; an exception, as the message explains, is a direct insert into a long-type column.
当尝试在 SQL 语句中使用长度超过 4000 字节的 varchar 变量时，会发生此错误。PL/SQL 允许最多 32767 字节的 varchars，但数据库表和 SQL 语言的限制是 4000。不能在 SQL 语句中使用 SQL 无法识别的 PL/SQL 变量；正如消息所解释的那样，一个例外是直接插入到长型列中。
create table test (v varchar2(10), c clob);
declare
shortStr varchar2(10) := '0123456789';
longStr1 varchar2(10000) := shortStr;
longStr2 varchar2(10000);
begin
for i in 1 .. 10000
loop
longStr2 := longStr2 || 'X';
end loop;
-- The following results in ORA-01461
insert into test(v, c) values(longStr2, longStr2);
-- This is OK; the actual length matters, not the declared one
insert into test(v, c) values(longStr1, longStr1);
-- This works, too (a direct insert into a clob column)
insert into test(v, c) values(shortStr, longStr2);
-- ORA-01461 again: You can't use longStr2 in an SQL function!
insert into test(v, c) values(shortStr, substr(longStr2, 1, 4000));
end;
回答by Markus1980Wien
A collegue of me and I found out the following:
我和我的一个同事发现了以下内容：
When we use the Microsoft .NET Oracle driver to connect to an oracle Database (System.Data.OracleClient.OracleConnection)
当我们使用 Microsoft .NET Oracle 驱动程序连接到一个 oracle 数据库时（System.Data.OracleClient.OracleConnection）
And we are trying to insert a string with a length between 2000 and 4000 characters into an CLOB or NCLOB field using a database-parameter
我们正在尝试使用数据库参数将长度在 2000 到 4000 个字符之间的字符串插入到 CLOB 或 NCLOB 字段中
oraCommand.CommandText = "INSERT INTO MY_TABLE (NCLOB_COLUMN) VALUES (:PARAMETER1)";
// Add string-parameters with different lengths
// oraCommand.Parameters.Add("PARAMETER1", new string(' ', 1900)); // ok
oraCommand.Parameters.Add("PARAMETER1", new string(' ', 2500)); // Exception
//oraCommand.Parameters.Add("PARAMETER1", new string(' ', 4100)); // ok
oraCommand.ExecuteNonQuery();
- any string with a length under 2000 characters will not throw this exception
- any string with a length of more than 4000 characters will not throw this exception
- only strings with a length between 2000 and 4000 characters will throw this exception
- 任何长度小于 2000 个字符的字符串都不会抛出此异常
- 任何长度超过 4000 个字符的字符串都不会抛出此异常
- 只有长度在 2000 到 4000 个字符之间的字符串才会抛出此异常
We opened a ticket at microsoft for this bug many years ago, but it has still not been fixed.
多年前，我们在 microsoft 为这个 bug 开了一张票，但它仍然没有修复。
回答by Murali
This ORA-01461 does not occur only while inserting into a Long column. This error can occur when binding a long string for insert into a VARCHAR2 column and most commonly occurs when there is a multi byte(means single char can take more than one byte space in oracle) character conversion issue.
此 ORA-01461 不会仅在插入 Long 列时发生。将要插入的长字符串绑定到 VARCHAR2 列时可能会发生此错误，并且最常发生在存在多字节（意味着单个字符在 oracle 中可能占用多个字节空间）字符转换问题时。
If the database is UTF-8 then, because of the fact that each character can take up to 3 bytes, conversion of 3 applied to check and so actually limited to use 1333 characters to insert into varchar2(4000).
如果数据库是UTF-8的话，因为每个字符最多可以占用3个字节，所以将3的转换应用于检查等实际上限制使用1333个字符插入到varchar2（4000）中。
Another solution would be change the datatype from varchar2(4000) to CLOB.
另一种解决方案是将数据类型从 varchar2(4000) 更改为 CLOB。
回答by aneela
回答by pahariayogi
Applications using JDBC 10.1 has got a bug (Doc ID 370438.1) and can throw the same ORA-01461 exception while working with UTF8 character set database even though inserted characters are less than the maximum size of the column.
使用 JDBC 10.1 的应用程序有一个错误 (Doc ID 370438.1)，并且在使用 UTF8 字符集数据库时可能会抛出相同的 ORA-01461 异常，即使插入的字符小于列的最大大小。
Recommended Solution: - Use 10gR2 JDBC drivers or higher in such case.
推荐的解决方案： - 在这种情况下使用 10gR2 JDBC 驱动程序或更高版本。
HTH
HTH
回答by mkb
Kiran's answer is definetely the answer for my case.
Kiran的答案绝对是我的案例的答案。
In code part I split string to 4000 char strings and try to put them in to db.
在代码部分，我将字符串拆分为 4000 个字符字符串并尝试将它们放入 db。
Explodes with this error.
因此错误而爆炸。
The cause of the error is using utf chars, those counts 2 bytes each. Even I truncate to 4000 chars in code(sth. like String.Take(4000)), oracle considers 4001 when string contains '?' or any other non-eng(non ascii to be precise, which are represented with two or bytes in utf8) characters.
错误的原因是使用 utf 字符，每个字符占 2 个字节。即使我在代码中截断为 4000 个字符（例如 String.Take(4000)），当字符串包含 '?' 时，oracle 也会考虑 4001 或任何其他非 eng（准确地说是非 ascii，用 utf8 中的两个或字节表示）字符。
回答by Martin Staufcik
I had the same problem with Entity Framework database first on all CLOB columns.
我首先在所有 CLOB 列上遇到了与实体框架数据库相同的问题。
As a workaround, I filled the text values with spaces to be at least 4000 in width in insert operations (did not come with any better solution).
作为一种解决方法，我在插入操作中用空格填充文本值，使其宽度至少为 4000（没有提供任何更好的解决方案）。
回答by Francisco M
In my particular case, I was trying to store a Base64 encoded file into a table BLOB field, using Mybatis.
在我的特殊情况下，我试图使用 Mybatis 将 Base64 编码的文件存储到表 BLOB 字段中。
So in my xml I had:
所以在我的 xml 我有：
<insert id="save..." parameterType="...DTO">
<selectKey keyProperty="id" resultType="long" order="BEFORE">
SELECT SEQ.nextVal FROM DUAL
</selectKey>
insert into MYTABLE(
ID,
...,
PDF
) values (
#{id, jdbcType=VARCHAR},
...,
#{tcPdf, jdbcType=BLOB},
)
</insert>
and in my DTO:
在我的 DTO 中：
String getPdf(){
return pdf;
}
That makes to Mybatis threat as if were a String char sequence and try to store it as a Varchar. So my solution was the following:
这使得 Mybatis 威胁好像是一个 String char 序列并尝试将其存储为 Varchar。所以我的解决方案如下：
In my DTO:
在我的 DTO 中：
Byte[] getPdf(){
return pdf.getBytes();
}
And worked.
并工作。
I hope this could help anybody.
我希望这可以帮助任何人。
