Skip to Content (Press Enter)
UserImage.jpg
Hoi Dinh Huu
21Views
1Comments
Solved
[SQL Sandbox] Oracle DB error in aggregate filter using Trim(ToLower(Replace(...)))
Question
Forge
Oracle
OutSystems 11
Traditional Web
sql-sandbox
Web icon
SQL Sandbox (O11)
Forge asset by Leonardo Fernandes
Application Type
Traditional Web

Dear Support Team,

I am experiencing an issue with SQL Sandbox in an Oracle DB-based environment.

When executing the action Execute that reaches the note AddToHistory, the system throws an error: "Error executing query. "

Based on my investigation, the error occurs in the GetStatementHistoriesByUserId aggregate. It appears to be caused by the filter expression:

Trim(ToLower(Replace(Replace(StatementHistory.Statement, NewLine(), ""), " ", "")))

Have you guys encountered this and is there a way to fix it?

Thanks!

0
0
18 Nov 2025
Copy Link
UserImage.jpg
Hoi Dinh Huu
Solution

Hi everyone,

I did some digging to figure out what was going on.

The root cause is that the Statement field has a length of 20,000, which makes Oracle treat it as a CLOB. That’s why the = operator doesn’t work in the Aggregate Filter - Oracle doesn’t support direct equality comparison on CLOB fields.

Here’s what worked for me:

  1. Option 1 - Use ListFilter: Remove the Filter from the Aggregate and apply the same condition using a ListFilter after fetching the data.
  2. Option 2 - Create a HASH attribute: When saving the Statement, I also store a HASH value of it in a separate attribute. Then, whenever I need to compare Statements, I just compare the HASH instead of the full text.

Hope this helps someone else facing the same issue!

0
0
19 Nov 2025
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.