ORA-00932: inconsistent datatypes: expected - got BLOB

ORA-00932: inconsistent datatypes: expected - got BLOB

Postby 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
flutos
 
Posts: 31
Joined: Tue 06 Oct 2009 21:55

Postby 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.jspa?threadID=830124.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby 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?
flutos
 
Posts: 31
Joined: Tue 06 Oct 2009 21:55

Postby 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle