ORA-00932: inconsistent datatypes: expected - got BLOB

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
flutos
Posts: 31
Joined: Tue 06 Oct 2009 21:55

ORA-00932: inconsistent datatypes: expected - got BLOB

Post by flutos » Wed 01 Jun 2011 21:32

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

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 02 Jun 2011 13:28

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.

flutos
Posts: 31
Joined: Tue 06 Oct 2009 21:55

Post by flutos » Thu 02 Jun 2011 14:31

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?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 03 Jun 2011 10:50

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.

Post Reply