[
https://issues.apache.org/jira/browse/CAY-2894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18037768#comment-18037768
]
Nikita Timofeev commented on CAY-2894:
--------------------------------------
Hi [~soliax]
Got no real access to the Oracle at the moment, so not able to fully check this.
You could try to use column directly instead of it's name, as DB could get
confused by field name bind as an argument:
{code:java}
functionCall("DBMS_LOB.INSTR", **QUERY_SQL.getExpression()**, "$" +
QUERY_SQL.getName());
{code}
Other that that I'm out of ideas what else could be wrong.
> 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
> Priority: Major
> Fix For: 4.2.3, 5.0-M2
>
>
> 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)
