Geek Answers Handbook

Oracle Database - ORA-01460 - Unrealized or Unreasonable Conversion Requested

I get an error: Unfulfilled 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 looked on the Internet and the only suggestion I could find was to use the to_clob instruction. Either I do not understand how this works, or it does not fix the problem. As far as I know, there should be no distortion of data types. The "user" field is the text, the "family" field is the text, and the "date" field is the DateTime in the database.

Any ideas are very welcome!

UPDATE This 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 just fine, although it is 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;

Decision

, "" . 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;
+5
3

, ...

DDL SQL TABLE_PART_NUM.FAMILY?

OdbcType.VarChar, OdbcType.NVarChar OdbcType.NText OdbcType.Text?

, NVARCHAR2 , VARCHAR2 - , "4000" 4000 , 4000 . 2000 .

SQL Developer. - ?

- "" ? ...

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

... NLS_CHARACTERSET VARCHAR2 NLS_NCHAR_CHARACTERSET NVARCHAR2.

ODBC Oracle? ?

, ODP.NET?

+4

, :

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

, DateTime:

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

, SQL :

TABLE_CASE.CREATION_TIME > ?
0

Use the sample code below. you need to create a temporary lob. it works for me

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();
}
0
source

More articles:


All Articles