lallemand created CAY-2894:
------------------------------
Summary: Issue with Oracle NCLOB Search in Version 4.2.2
Key: CAY-2894
URL: https://issues.apache.org/jira/browse/CAY-2894
Project: Cayenne
Issue Type: Bug
Components: Core Library
Affects Versions: 4.2.2
Reporter: lallemand
Dear,
In the latest stable version (4.2.2), we are encountering an issue when
performing a search on Oracle *{{NCLOB}}* fields.
Previously, this functionality worked correctly, and it still behaves as
expected on SQL Server using *{{{}nvarchar(max){}}}.*
h3. The Issue
The problem arises when executing a query with a {{LIKE}} expression, such as:
{code:java}
public static KycRuleCommon getFromNameAndQuery(String pm_name, String
pm_query) throws BaseException
{
try {
if (pm_name == null || pm_name.compareTo("")==0 || pm_query == null
|| pm_query.compareTo("")==0) return null;
Map<String, Object> parameters = new HashMap<>();
parameters.put(NAME.getName(), pm_name);
parameters.put(QUERY_SQL.getName(), pm_query);
ObjectSelect<KycRuleCommon> query =
ObjectSelect.query(KycRuleCommon.class, ExpressionFactory.exp(
NAME.getName() + " like $" + NAME.getName()
+ " and " + QUERY_SQL.getName() + " like $" +
QUERY_SQL.getName()).params(parameters));
ObjectContext context = BaseContext.getThreadObjectContext();
return context.selectFirst(query);
} catch (Exception e) {
throw new EntityException(new EventSysm1023(e));
}
} {code}
We understand that using *{{LIKE}}* on *{{NCLOB}}* fields is generally
discouraged, especially if the value exceeds 4000 characters, as this can lead
to runtime errors. However, even when the value is well under that limit, it
now fails on Oracle, while still working on SQL Server.
This suggests that a recent change in parameter binding may be forcing Oracle
to treat the field as an *NCLOB* explicitly, causing the failure regardless of
content size.
Normally, in Oracle, such queries should use *{{DBMS_LOB.INSTR()}}* for safe
processing.
h3. Working Queries in Oracle
The following native SQL queries work correctly on our database and bring one
record:
{code:java}
SELECT * FROM TKYCRULECOMMON t WHERE t.name LIKE 'name' AND t.QUERY_SQL LIKE
'%test%';
SELECT * FROM TKYCRULECOMMON t WHERE t.name LIKE 'name' AND
DBMS_LOB.INSTR(t.QUERY_SQL, 'test') > 0; {code}
h3. Attempt with {{FunctionExpressionFactory}}
I attempted to adapt the logic using
{*}{{FunctionExpressionFactory.functionCall()}}{*}, but I cannot get it to
return any results. The query doesn't crash and seems to be generated
correctly, but always yields no records.
Here is the updated code and the resulting SQL:
{code:java}
public static KycRuleCommon getFromNameAndQuery(String pm_name, String
pm_query) throws BaseException
{
try {
if (pm_name == null || pm_name.compareTo("")==0 || pm_query == null
|| pm_query.compareTo("")==0) return null;
Map<String, Object> parameters = new HashMap<>();
parameters.put(NAME.getName(), pm_name);
parameters.put(QUERY_SQL.getName(), pm_query);
Expression dbmsExp =
FunctionExpressionFactory.functionCall("DBMS_LOB.INSTR",QUERY_SQL.getName(),"$"
+ QUERY_SQL.getName());
Expression compareExp = ExpressionFactory.greaterExp(dbmsExp, 0);
Expression nameExp = ExpressionFactory.exp(NAME.getName() + " like $" +
NAME.getName());
ObjectSelect<KycRuleCommon> query =
ObjectSelect.query(KycRuleCommon.class,
compareExp.andExp(nameExp).params(parameters));
query.cacheStrategy(QueryCacheStrategy.NO_CACHE);
ObjectContext context = BaseContext.getThreadObjectContext();
return context.selectFirst(query);
} catch (Exception e) {
throw new EntityException(new EventSysm1023(e));
}
} {code}
Generated SQL:
{code:java}
SELECT t0.created_by c0, t0.date_created c1, t0.date_modified c2,
t0.description c3, t0.dynfield_identifier c4, t0.modified_by c5, t0.name c6,
t0.query_sql c7, RTRIM(t0.standard) c8, t0.version c9, t0.id c10 FROM
tKycRuleCommon t0 WHERE (DBMS_LOB.INSTR(: 1,: 2) >: 3) AND t0.name LIKE: 4
{code}
Despite the query looking correct, no results are returned. Using SQL Template
we do retrieve correctly information.
Could you please clarify:
* Am I using *{{FunctionExpressionFactory.functionCall()}}* correctly in this
context?
* Is this a known limitation or a potential bug introduced in version 4.2.2?
* Is there an alternative/best-practice approach when working with NCLOBs and
expressions like this?
Thank you in advance for your help.
Best regards,
Anton
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
