Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0013153Openbravo ERPA. Platformpublic2010-04-29 09:452021-10-05 18:08
0013153: DAL queries don't work with CLOB (oracle)
If a column in (ora) DB is defined as CLOB type, it is not possible to use in DAL queries.

I haven't tested the behavior in PostgreSQL where there CLOB is converted into TEXT type.

Note that the java object is correctly composed and populated.

This means this code doesn't work:

     OBCriteria<Preference> qPref = OBDal.getInstance().createCriteria(Preference.class);
     qPref.add(Expression.eq(Preference.PROPERTY_ATTRIBUTE, "testProperty"));
     qPref.add(Expression.eq(Preference.PROPERTY_SEARCHKEY, "anotherValue"));
     Preference newPref = qPref.list().get(0);

whereas this other one does work:

Preference newPref = null;
    OBCriteria<Preference> qPref = OBDal.getInstance().createCriteria(Preference.class);
    qPref.add(Expression.eq(Preference.PROPERTY_ATTRIBUTE, "testProperty"));
    for (Preference p : qPref.list()) {
      if (p.getSearchKey().equals("anotherValue")) {
        newPref = p;

The following exception is raised for queries:

org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(
    at org.hibernate.exception.JDBCExceptionHelper.convert(
    at org.hibernate.loader.Loader.doList(
    at org.hibernate.loader.Loader.listIgnoreQueryCache(
    at org.hibernate.loader.Loader.list(
    at org.hibernate.loader.criteria.CriteriaLoader.list(
    at org.hibernate.impl.SessionImpl.list(
    at org.hibernate.impl.CriteriaImpl.list(
    at org.openbravo.dal.service.OBCriteria.list(
-Create a CLOB column in DB and in AD
-Use it in DAL queries.
No tags attached.
related to defect 0015555 closed alostale In Oracle, Enterprise Module Management window shows an error message on open 
has duplicate defect 0015556 closed mtaal OBCriteria is not able to filter Oracle's CLOB datatype 
Issue History
2010-04-29 09:45alostaleNew Issue
2010-04-29 09:45alostaleAssigned To => mtaal
2010-05-03 08:27alostaleStatusnew => scheduled
2010-05-05 17:17shuehnerNote Added: 0026960
2010-05-05 17:19shuehnerIssue Monitored: shuehner
2011-01-05 08:29alostaleRelationship addedhas duplicate 0015556
2011-01-05 08:30alostaleRelationship addedrelated to 0015555
2011-04-05 21:27mtaalAssigned Tomtaal => alostale
2011-06-27 17:44shuehnerNote Added: 0038545
2011-11-23 10:41alostaleTypedefect => design defect
2012-09-24 23:35AugustoMauchNote Added: 0052497
2012-09-24 23:35AugustoMauchPriorityurgent => normal
2017-03-31 14:36alostaleStatusscheduled => acknowledged
2017-04-10 14:34alostaleAssigned Toalostale => platform
2021-10-05 18:08cbernerNote Added: 0132173

2010-05-05 17:17   
After resolving this issue an reintegrating the pi-preference branch: the junit test PreferenceTest should be updated (search for 0013153 in it)
2011-06-27 17:44   
Just retested that on latest pi. This bug is still present with the new hibernate version.
2012-09-24 23:35   
Effort: 10
Impact: low
Plan: mid
2021-10-05 18:08   
Still happening after updating hibernate to version 5.5.7. Tried running the PreferenceTest with the code reintegrated and got this result:

[junit] 18:07:09.893 INFO org.openbravo.test.base.OBBaseTest - *** Starting test case: org.openbravo.test.preference.PreferenceTest.test14SolvedConflict
[junit] 18:07:09.932 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00932: inconsistent datatypes: expected - got CLOB