0018517: CLOB fields fails on oracle in the grid

Description

See this description:
http://wiki.openbravo.com/wiki/Common_Issues_Tips_and_Tricks#Grids_with_CLOB_fields_fail_on_Oracle [^]

Steps To Reproduce

Research why not showing in the grid actually makes a difference, it should not.

Find a solution in the query generator used in the ui, see the following remarks from an email exchange with Asier:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
This is an old known issue [1]. The problem is Oracle raises ORA-00932: inconsistent datatypes: expected - got CLOB when comparing (equals) a CLOB with a (var)char.

So this query fails:

select * from ad_preference where value = 'N';

whereas these other ones work:

select * from ad_preference where value like 'N';
select * from ad_preference where to_char(value) = 'N';

In case you can do a hql instead of using a OBCriteria you can just add a to_char or replace = with like and you are happy.

I guess the problem is we don't define in AD which is the actual DB column type, so DAL Criteria cannot do a different query for CLOBs and I don't know how would affect performance (but my guess is that not very good) changing all = with like or to_char. We might do it in the catch in case the criteria query fails... Or we might put a flag when generating the class (just for Oracle?) reading from DB catalog to treat this case differently.

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0040926) hgbot (developer) 2011-09-13 11:37	Repository: erp/devel/pi Changeset: ac37116c4cc26350303c76dec7e0ac76f217b484 Author: Martin Taal <martin.taal <at> openbravo.com> Date: Tue Sep 13 11:36:48 2011 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/ac37116c4cc26350303c76dec7e0ac76f217b484 [^] Fixes issue 18517: CLOB fields fails on oracle in the grid Prevent filtering and sorting on clob fields, to prevent errors in oracle --- M modules/org.openbravo.client.application/src/org/openbravo/client/application/window/OBViewGridComponent.java M modules/org.openbravo.client.kernel/src/org/openbravo/client/kernel/reference/TextUIDefinition.java ---

(0041661) hudsonbot (viewer) 2011-10-12 01:58	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/9bd2b5c2d982 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2011-09-13 10:10	mtaal	New Issue
2011-09-13 10:10	mtaal	Assigned To	=> mtaal
2011-09-13 10:10	mtaal	Modules	=> Core
2011-09-13 10:10	mtaal	OBNetwork customer	=> No
2011-09-13 11:37	hgbot	Checkin
2011-09-13 11:37	hgbot	Note Added: 0040926
2011-09-13 11:37	hgbot	Status	new => resolved
2011-09-13 11:37	hgbot	Resolution	open => fixed
2011-09-13 11:37	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/ac37116c4cc26350303c76dec7e0ac76f217b484 [^]
2011-10-03 11:29	marvintm	Status	resolved => closed
2011-10-12 01:58	hudsonbot	Checkin
2011-10-12 01:58	hudsonbot	Note Added: 0041661