Page 1 of 1

ORA-00932: inconsistent datatypes: expected - got BLOB

Posted: Wed 01 Jun 2011 21:32
by flutos
When I try to query against a blob field I get this erorr. is there something that Im doing wrong. This is a sample that gives the error:

[Test]
public void sqlQueryTest()
{

string queryString = "select entityroot0_.ID " +
" from TESTSCOTT3.ROOTTESTOBJECT entityroot0_ where entityroot0_.ARRAY1=:p0 ";
string connectionString = "User Id=testscott3;Password=mypass;Direct=true;Data Source=drms-oracle;SID=oracle;Port=1521;Unicode=True;Pooling=True;";

using (Devart.Data.Oracle.OracleConnection connection =
new OracleConnection(connectionString))
{
// Create the Command and Parameter objects.
byte[] b1 = new byte[]{1,2,3,4,5};
OracleCommand command = new OracleCommand(queryString, connection);
OracleParameter p1 = new OracleParameter("p0" ,b1);
p1.OracleDbType = OracleDbType.Blob;
command.Parameters.Add(p1);
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}",
reader[0]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}

just create a table where the Array1 field is of type Blob.

Im using the most current devart driver 6.30 and oracle 11.2

Posted: Thu 02 Jun 2011 13:28
by Shalex
Please try using

Code: Select all

string queryString = "select entityroot0_.ID " +
" from TESTSCOTT3.ROOTTESTOBJECT entityroot0_ where dbms_lob.compare(entityroot0_.ARRAY1,:p0,5,1,1) = 0";
instead of

Code: Select all

string queryString = "select entityroot0_.ID " +
" from TESTSCOTT3.ROOTTESTOBJECT entityroot0_ where entityroot0_.ARRAY1=:p0 ";
For more information, refer to http://forums.oracle.com/forums/thread. ... dID=830124.

Posted: Thu 02 Jun 2011 14:31
by flutos
Well the good news is I tried this and it worked. The bad news is Im using an ORM(nhibernate) so Im not sure how I can get nhibernate to use this. Do you have any insight on how to do this with nhibernate. Is there any way at all to get this to work with the '=' compare?

Posted: Fri 03 Jun 2011 10:50
by Shalex
We recommend you to avoid comparing BLOBs in the WHERE clause or contact the NHibernate support team concerning generation of the invalid SQL for comparing BLOBs by this ORM.