Oracle 数据库 - ORA-01460 - 请求未实现或不合理的转换

Oracle database - ORA-01460 - unimplemented or unreasonable conversion requested

提问by DarkShadow

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

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.

我在网上环顾四周，我能找到的唯一建议是使用 to_clob 语句。要么我不明白它是如何工作的，要么不能解决问题。据我所知，不应该有任何数据类型的覆盖。'user' 字段是文本，'family' 字段是文本，'date' 字段是数据库中的 DateTime。

Any ideas are very apreciated!

任何想法都非常感谢！

UPDATEThis code works:

更新此代码有效：

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;

UPDATE (AGAIN)

更新（再次）

This code also works perfectly although vulnerable to SQL injection.

尽管容易受到 SQL 注入攻击，但此代码也能完美运行。

        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;

SOLUTION

解决方案

I did not realize that 'text' wasn't a true type. Changing to NVARCHAR did the trick:

我没有意识到“文本”不是真正的类型。更改为 NVARCHAR 可以解决问题：

        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;

采纳答案by Branko Dimitrijevic

Some questions, guesses and suggestions...

一些问题，猜测和建议......

What is the exact DDL SQL type for TABLE_PART_NUM.FAMILY?

确切的 DDL SQL 类型是TABLE_PART_NUM.FAMILY什么？

Did you try using OdbcType.VarChar, OdbcType.NVarCharor even OdbcType.NTextinstead of OdbcType.Text?

您是否尝试使用OdbcType.VarChar,OdbcType.NVarChar甚至OdbcType.NText代替OdbcType.Text？

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.

另外，请注意，默认情况下 NVARCHAR2 大小以字符为单位，而 VARCHAR2 以字节为单位 - 也许您的代码中的“4000”被解释为 4000 个字符，超过了 4000字节字符数据的最大字段宽度。仅出于测试目的尝试使用 2000 甚至更低的数字。

Try to execute the query from the SQL Developer. Do you have any problems there?

尝试从 SQL Developer 执行查询。你那里有什么问题吗？

Do you use any "unusual" character encoding in your database? You can execute...

您是否在数据库中使用了任何“不寻常”的字符编码？你可以执行...

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET';

...and look at NLS_CHARACTERSETfor VARCHAR2 encoding and NLS_NCHAR_CHARACTERSETfor NVARCHAR2 encoding.

...并查看NLS_CHARACTERSETVARCHAR2 编码和NLS_NCHAR_CHARACTERSETNVARCHAR2 编码。

What are the exact versions of your ODBC driver and Oracle server? Do they match?

您的 ODBC 驱动程序和 Oracle 服务器的确切版本是什么？他们匹配吗？

Do you get this problem if you try using the equivalent ODP.NET code?

如果您尝试使用等效的 ODP.NET 代码，是否会遇到此问题？

回答by Sreekanthreddy KURRI

Use below sample code. you need to create temporary LOB. this works for me

使用下面的示例代码。您需要创建临时 LOB。这对我有用

using System.Data.OracleClient;

OracleConnection objConnection = new OracleConnection();
OracleCommand objCommand = new OracleCommand();
try
{
    objConnection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["YourConnectionString"].ToString();

    if (objConnection.State != System.Data.ConnectionState.Open)
    {
        objConnection.Open();
    }

    objCommand.Connection = objConnection;

    //Create Temporary LOB @Start
    //Error Without Temp LOB { ORA-01460: unimplemented or unreasonable conversion requested }
    objCommand.CommandText = "DECLARE dpBlob BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(dpBlob, False, 0); :tmpBlob := dpBlob; END;";
    objCommand.Parameters.Add(new OracleParameter("tmpBlob", OracleType.Blob)).Direction = System.Data.ParameterDirection.Output;
    objCommand.ExecuteNonQuery();
    OracleLob tempLob = default(OracleLob);
    tempLob = (OracleLob)objCommand.Parameters[0].Value;
    tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
    tempLob.EndBatch();
    objCommand.Parameters.Clear();
    //Create Temporary LOB @End

    objCommand.CommandType = System.Data.CommandType.StoredProcedure;
    objCommand.CommandText = "INSERT_BLOB";

    objCommand.Parameters.AddWithValue("IN_USERNAME", "Sample Name");
    objCommand.Parameters.AddWithValue("IN_UPLOADED_BY", "Sample Name");

    string excelFileName = FileUpload1.PostedFile.FileName;
    int intlength = FileUpload1.PostedFile.ContentLength;

    Byte[] byteData = new Byte[intlength];
    FileUpload1.PostedFile.InputStream.Read(byteData, 0, intlength);

    objCommand.Parameters.AddWithValue("IN_ATTACH_FILE_ORIGINAL", excelFileName);
    objCommand.Parameters.Add("IN_ATTACH_BLOB_ORIGINAL", OracleType.Blob).Value = tempLob;

    objCommand.ExecuteNonQuery();

}
catch (Exception ex)
{
}
finally
{
    objCommand.Parameters.Clear();
    if (objConnection.State != System.Data.ConnectionState.Closed)
        objConnection.Close();
}

回答by Jon Skeet

I suspectit's because you're using this:

我怀疑这是因为你正在使用这个：

TABLE_CASE.CREATION_TIME > to_date(?,'MM/DD/YYYY HH:MI:SS AM')

which expects a stringparameter, but you're already passing in a DateTimevalue:

它需要一个字符串参数，但您已经传入了一个DateTime值：

FindCases.Parameters.Add(@"date", OdbcType.DateTime, 4000).Value = date;

I suspect you can just change your SQL to use:

我怀疑您可以更改 SQL 以使用：

TABLE_CASE.CREATION_TIME > ?