oracle ORA-01460: 请求未实现或不合理的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11036300/
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-01460: unimplemented or unreasonable conversion requested
提问by Noam
When I run the following .Net code:
当我运行以下 .Net 代码时：
using (var c = Shared.DataSources.BSS1.CreateCommand())
{
c.CommandText = "\r\nSelect c1, c2, c3, rowid \r\nFrom someSpecificTable \r\nWhere c3 = :p0";
var p = c.CreateParameter() as Oracle.DataAccess.Client.OracleParameter;
c.Parameters.Add(p);
p.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
p.DbType = System.Data.DbType.AnsiString;
p.Size = 20;
p.Value = "007";
p.ParameterName = ":p0";
using (var r = c.ExecuteReader())
{
r.Read();
}
}
I get the following error:
我收到以下错误：
ORA-01460: unimplemented or unreasonable conversion requested
ORA-02063: preceding line from XXX
This is not my database, and I don't have control over the select statements that I get, that table IS from a database link.
这不是我的数据库，我无法控制我获得的选择语句，该表来自数据库链接。
The funny thing is that if I add the following code just before the ExecuteReader it runs fine.
有趣的是，如果我在 ExecuteReader 之前添加以下代码，它运行良好。
c.CommandText = c.CommandText.Replace("\r\n", " ");
Unfortunately that is not a good solution in my case as I can't control to SQL nore can I change it that way.
不幸的是，在我的情况下，这不是一个好的解决方案，因为我无法控制 SQL，我也不能那样改变它。
As for the table itself, the columns are: c1 Number(5) c2 varchar2(40) c3 varchar2(20).
至于表本身，列是： c1 Number(5) c2 varchar2(40) c3 varchar2(20)。
I know that ORA-02063 that comes after indicate something about a database link, but I looked in the synonim table and it didn't come from any database_link, and also I don't think that \r\n should affect database link.
我知道后面的 ORA-02063 表示数据库链接，但我查看了 Synonim 表，它不是来自任何 database_link，而且我认为 \r\n 不应该影响数据库链接。
I tried running the query without bound parameters, and it did work - but again bad practice to do so in a general term.
我尝试在没有绑定参数的情况下运行查询，并且它确实有效 - 但在一般情况下再次这样做是不好的做法。
The trouble is that a competing tool that is not .Net based, is working and thus it's not a general problem.
问题在于，一个不是基于 .Net 的竞争工具正在运行，因此它不是一个普遍的问题。
I also couldn't reproduce the problem in my own environment, this is a customer database and site. I am using instant client 11.1.6.20 and also tested it with instant client 11.2.3.0
我也无法在我自己的环境中重现该问题，这是一个客户数据库和站点。我正在使用即时客户端 11.1.6.20 并使用即时客户端 11.2.3.0 对其进行了测试
The db is 10 and the db link is to an oracle v8 database
db 为 10，db 链接指向 oracle v8 数据库
Any help would be appreciated
任何帮助，将不胜感激
采纳答案by Noam
Finally I found the answer!!!
终于找到答案了！！！
After investigating and reflecting into the code I found that by changing the Direction of the Parameter to input output - the problem was resolved.
在调查并反映到代码中后，我发现通过将参数的方向更改为输入输出 - 问题得到了解决。
p.Direction = ParameterDirection.InputOutput;
回答by Kiran
This problem can be recreated with straight forward steps. That is, any SQL query having a string literal, in where clause, more than 4000 characters in length gives an error "ORA-01704: string literal too long"
这个问题可以通过直接的步骤重新创建。也就是说，任何具有字符串文字的 SQL 查询，在 where 子句中，长度超过 4000 个字符都会给出错误“ORA-01704：字符串文字太长”
But, when the same query is executed through JDBC it gives "ORA-01460: unimplemented or unreasonable conversion requested"
但是，当通过 JDBC 执行相同的查询时，它会给出“ORA-01460：请求未实现或不合理的转换”
回答by Noam
After much investigation I found out that it's all about the fact that we have bound parameters that are used from ODP.NET and targeting tables from a DBLINK to a V8 Oracle server.
经过大量调查，我发现这完全是因为我们绑定了从 ODP.NET 使用的参数并将表从 DBLINK 定位到 V8 Oracle 服务器。
Once I eliminated the bound parameters it all worked.
一旦我消除了绑定参数，它就全部起作用了。
It was a while back, but I think it's had something to do with varying string lengths of the strings sent to the bound parameter. It seems that it ignored the size property, so if in the first query I sent a string with length 10 and in the second string I sent a string with length 12, I'll get that error.
很久以前，但我认为这与发送到绑定参数的字符串的字符串长度不同有关。似乎它忽略了 size 属性，所以如果在第一个查询中我发送了一个长度为 10 的字符串，而在第二个字符串中我发送了一个长度为 12 的字符串，我会得到那个错误。
I also found the oracle articles about it : https://community.oracle.com/thread/2460796?tstart=0
我还找到了关于它的 oracle 文章：https: //community.oracle.com/thread/2460796?tstart=0
and the patch for it: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=745005.1
以及它的补丁：https: //support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=745005.1
But - I found a fix in my code that actually solved it - see my next answer.
但是 - 我在我的代码中找到了一个实际解决它的修复程序 - 请参阅我的下一个答案。
Hope this helps anyone.
希望这可以帮助任何人。
回答by AdamRossWalker
The accepted answer didn't work for me. However, after reading the attached links, I applied the following – although it does involve editing the SQL.
接受的答案对我不起作用。然而，在阅读了附加的链接后，我应用了以下内容——尽管它确实涉及编辑 SQL。
In my case, I knew the maximum left of the bind variable (the length reducing after the first call is what causes the issue). So I padded the .NET string, and added a TRIMin the SQL. Following your example:
就我而言，我知道绑定变量的最大左侧（第一次调用后长度减少是导致问题的原因）。所以我填充了 .NET 字符串，并TRIM在 SQL 中添加了一个。按照你的例子：
c.CommandText = "\r\nSelect c1, c2, c3, rowid \r\nFrom someSpecificTable \r\nWhere c3 = TRIM(:p0)";
...
p.Value = "007".PadRight(10);
