Oracle数据库-ORA-01460-请求未实现或不合理的转换 [英] Oracle database - ORA-01460 - unimplemented or unreasonable conversion requested
问题描述
我收到一条错误消息:使用以下代码请求了未实现或不合理的转换:
I'm getting an error message: unimplemented or unreasonable conversion requested using the following code:
OdbcConnection oConn = new OdbcConnection();
oConn.ConnectionString = @"Driver={Oracle ODBC Driver};Data Source=*****;UID=********;PWD=******;DBQ=*****;DBA=R;APA=T;FEN=T;QTO=F;FRC=10;FDL=10;LOB=F;RST=T;FRL=T;MTS=F;CSR=F;PFC=10;TLO=0;";
oConn.Open();
string user = "ANYUSER";
string family = "ANYFAMILY";
DateTime date = DateTime.Today;
OdbcCommand FindCases = new OdbcCommand(@"select TABLE_CASE.ID_NUMBER, TABLE_USER.LOGIN_NAME
from TABLE_CASE, TABLE_USER, TABLE_PRIVCLASS, TABLE_CONDITION, TABLE_PART_NUM
where TABLE_CASE.CASE_ORIGINATOR2USER=TABLE_USER.OBJID and TABLE_CASE.CASE_STATE2CONDITION=TABLE_CONDITION.OBJID and TABLE_CASE.CASE_PRT2PART_INFO=TABLE_PART_NUM.OBJID
and TABLE_USER.USER_ACCESS2PRIVCLASS=TABLE_PRIVCLASS.OBJID and TABLE_USER.LOGIN_NAME=? and TABLE_PART_NUM.FAMILY=? and TABLE_CONDITION.S_TITLE='CLOSED' and TABLE_CASE.CREATION_TIME > to_date(?,'MM/DD/YYYY HH:MI:SS AM')", oConn);
FindCases.CommandType = System.Data.CommandType.Text;
FindCases.Parameters.Add(@"user", OdbcType.Text, 4000).Value = user;
FindCases.Parameters.Add(@"family", OdbcType.Text, 4000).Value = family;
FindCases.Parameters.Add(@"date", OdbcType.DateTime, 4000).Value = date;
if (oConn.State == System.Data.ConnectionState.Open)
{
try
{
OdbcDataReader readCases = FindCases.ExecuteReader(); //errors at this line
我在网上环顾四周，我唯一能找到的建议是使用to_clob语句.我不明白它是如何工作的，或者那不能解决问题.据我所知，数据类型不应该涉及任何范围.数据库中的用户"字段是文本，家庭"字段是文本，日期"字段是DateTime.
I have looked around online and the only suggestion I could find was using a to_clob statement. Either I don't understand how it works or that doesn't fix the issue. To my knowledge there shouldn't be any coversion of data types. The 'user' field is text, the 'family' field is text, and the 'date' field is DateTime in the database.
任何想法都很珍贵！
更新 这段代码有效:
OdbcCommand FindCases = new OdbcCommand(@"select TABLE_CASE.ID_NUMBER, TABLE_USER.LOGIN_NAME
from TABLE_CASE, TABLE_USER, TABLE_PRIVCLASS, TABLE_CONDITION, TABLE_PART_NUM
where TABLE_CASE.CASE_ORIGINATOR2USER=TABLE_USER.OBJID and TABLE_CASE.CASE_STATE2CONDITION=TABLE_CONDITION.OBJID and TABLE_CASE.CASE_PRT2PART_INFO=TABLE_PART_NUM.OBJID
and TABLE_USER.USER_ACCESS2PRIVCLASS=TABLE_PRIVCLASS.OBJID and TABLE_USER.LOGIN_NAME=? and TABLE_PART_NUM.FAMILY='Desktop' and TABLE_CONDITION.S_TITLE='CLOSED' and TABLE_CASE.CREATION_TIME > ?", oConn);
FindCases.CommandType = System.Data.CommandType.Text;
FindCases.Parameters.Add(@"user", OdbcType.Text, 4000).Value = user;
//FindCases.Parameters.Add(@"family", OdbcType.Text, 4000).Value = family;
FindCases.Parameters.Add(@"date", OdbcType.DateTime, 4000).Value = date;
更新(再次)
尽管容易受到SQL注入的影响，但此代码也可以完美地工作.
This code also works perfectly although vulnerable to SQL injection.
OdbcCommand FindCases = new OdbcCommand(@"select TABLE_CASE.ID_NUMBER
from TABLE_CASE, TABLE_USER, TABLE_PRIVCLASS, TABLE_CONDITION, TABLE_PART_NUM
where TABLE_CASE.CASE_ORIGINATOR2USER=TABLE_USER.OBJID and TABLE_CASE.CASE_STATE2CONDITION=TABLE_CONDITION.OBJID and TABLE_CASE.CASE_PRT2PART_INFO=TABLE_PART_NUM.OBJID
and TABLE_USER.USER_ACCESS2PRIVCLASS=TABLE_PRIVCLASS.OBJID and TABLE_USER.LOGIN_NAME=? and TABLE_PART_NUM.FAMILY='" + family + "' and TABLE_CONDITION.S_TITLE='CLOSED' and TABLE_CASE.CREATION_TIME > ?", oConn);
FindCases.CommandType = System.Data.CommandType.Text;
FindCases.Parameters.Add(@"user", OdbcType.Text, 4000).Value = user; //field size 30, text
//FindCases.Parameters.Add(@"family", OdbcType.Text, 4000).Value = family; //field size 20, text
FindCases.Parameters.Add(@"date", OdbcType.DateTime, 4000).Value = date;
解决方案
我没有意识到'文本'不是真正的类型.更改为NVARCHAR可以达到目的:
I did not realize that 'text' wasn't a true type. Changing to NVARCHAR did the trick:
OdbcCommand FindCases = new OdbcCommand(@"select TABLE_CASE.ID_NUMBER
from TABLE_CASE, TABLE_USER, TABLE_CONDITION, TABLE_PART_NUM
where TABLE_CASE.CASE_ORIGINATOR2USER=TABLE_USER.OBJID and TABLE_CASE.CASE_STATE2CONDITION=TABLE_CONDITION.OBJID and TABLE_CASE.CASE_PRT2PART_INFO=TABLE_PART_NUM.OBJID
and TABLE_USER.LOGIN_NAME=? and TABLE_PART_NUM.FAMILY=? and TABLE_CONDITION.S_TITLE='CLOSED' and TABLE_CASE.CREATION_TIME > ?", oConn);
FindCases.CommandType = System.Data.CommandType.Text;
FindCases.Parameters.Add(@"user", OdbcType.NVarChar, 30).Value = user; //field size 30, text
FindCases.Parameters.Add(@"family", OdbcType.NVarChar, 20).Value = family; //field size 20, text
FindCases.Parameters.Add(@"date", OdbcType.DateTime, 4000).Value = date;
推荐答案
一些问题，猜测和建议...
TABLE_PART_NUM.FAMILY的确切DDL SQL类型是什么?
What is the exact DDL SQL type for TABLE_PART_NUM.FAMILY?
您尝试使用OdbcType.VarChar，OdbcType.NVarChar或什至OdbcType.NText而不是OdbcType.Text吗?
Did you try using OdbcType.VarChar, OdbcType.NVarChar or even OdbcType.NText instead of OdbcType.Text?
此外，请注意，默认情况下，NVARCHAR2的大小以字符为单位，而VARCHAR2的以字节为单位-也许代码中的"4000"被解释为4000个字符，超出了字符数据的最大字段宽度4000个字节.尝试将2000甚至更低的数字用于测试目的.
Also, please note that by default NVARCHAR2 size is in characters but VARCHAR2 is in bytes - maybe "4000" in your code is interpreted as 4000 characters, exceeding the maximal field width for character data of 4000 bytes. Try using 2000 or even lower number just for testing purposes.
尝试从SQL Developer执行查询.那里有什么问题吗?
Try to execute the query from the SQL Developer. Do you have any problems there?
您是否在数据库中使用任何异常"字符编码?您可以执行...
Do you use any "unusual" character encoding in your database? You can execute...
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET';
...然后查看NLS_CHARACTERSET表示VARCHAR2编码，而NLS_NCHAR_CHARACTERSET表示NVARCHAR2编码.
...and look at NLS_CHARACTERSET for VARCHAR2 encoding and NLS_NCHAR_CHARACTERSET for NVARCHAR2 encoding.
ODBC驱动程序和Oracle服务器的确切版本是什么?他们匹配吗?
What are the exact versions of your ODBC driver and Oracle server? Do they match?
如果尝试使用等效的ODP.NET代码，会遇到此问题吗?
Do you get this problem if you try using the equivalent ODP.NET code?
这篇关于Oracle数据库-ORA-01460-请求未实现或不合理的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
