0015556: OBCriteria is not able to filter Oracle's CLOB datatype

Description

In Oracle trying to use OBCriteria to filter a CLOB datatype property, the following error is raised:

ORA-00932: inconsistent datatypes: expected - got CLOB

The problem is Oracle doesn't accept this syntax:

select * from myTable where ClobColumn = 'anyText';

It requires this other one instead (works in Oracle 11g, needs to be tested in 9g):

select * from myTable where to_char(ClobColumn) = 'anyText';

Steps To Reproduce

This snippet taken from org.openbravo.erpCommon.modules.ModuleReferenceDataOrgTree class before fixing issue 0015555 fails:

OBCriteria<ADOrgModule> adOrgModCriteria = OBDal.getInstance().createCriteria(
          ADOrgModule.class);
OBDal.getInstance()
          .get(Organization.class, strOrg)));
      adOrgModCriteria.add(Expression.eq(ADOrgModule.PROPERTY_CHECKSUM, checksum));
adOrgModCriteria.list();

Proposed Solution

Is this a DAL issue, or is it in the Hibernate side?

Workaround:

Instead of using OBCriteria to filter CLOB columns, use OBQuery adding the to_char DB function:

String whereClause = "as m where m.module.id = :module and m.client.id=:client and m.organization.id=:org and to_char(m.checksum)=:checksum";
OBQuery<ADOrgModule> cOrgModule = OBDal.getInstance().createQuery(ADOrgModule.class,
whereClause);

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

duplicate of	design defect	0013153		acknowledged	Triage Platform Base	DAL queries don't work with CLOB (oracle)
related to	defect	0015555		closed	alostale	In Oracle, Enterprise Module Management window shows an error message on open

Notes
There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2010-12-31 09:15	alostale	New Issue
2010-12-31 09:15	alostale	Assigned To	=> mtaal
2010-12-31 09:15	alostale	Modules	=> Core
2010-12-31 09:17	alostale	Proposed Solution updated
2010-12-31 09:18	alostale	Relationship added	related to 0015555
2011-01-04 08:53	alostale	Status	new => scheduled
2011-01-05 08:29	alostale	Relationship added	duplicate of 0013153
2011-01-05 08:29	alostale	Status	scheduled => closed
2011-01-05 08:29	alostale	Resolution	open => duplicate
2011-01-06 00:00	anonymous	sf_bug_id	0 => 3152155