AnsweredAssumed Answered

case insensitive search against Oracle backend on AGS using MapServer/Query?

Question asked by eliprandi on Nov 8, 2013
Latest reply on Nov 11, 2013 by eliprandi
We are an ISV and recently built an application that, among other things, allows you to search your features, using the simple MapServer/Query option (documented here). All we do is pass in the text parameter and let Esri do the work.
It works great on SQL Server as it performs a case insensitive search of the display field (we haven't verified, but we assume this follows the SQL Server configuration of collation). However, I am at a loss to try to get it to query an Oracle backend and ignore the case of the search text.

Yes, we might have to convert to using the WHERE clause instead, but as an ISV, this seems to be wrong choice as we might have to build different WHERE clauses depending on the RDBMS backend (or is UPPER('field name') LIKE UPPER('%term%') truly universal - at least SQL Server and Oracle?)

So, is there a simple way to have this MapServer/Query with text be case insensitive in Oracle? I have tried setting environment variables NLS_COMP and NLS_SORT to LINGUISTIC on the ArcGIS Server (Windows) but even after a full restart, it doesn't seem to have taken them. I also tried setting NLS_COMP and NLS_SORT values in the registry key HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1, but no luck there either.

Our environments would be:

  • ArcGIS 10.2 - Windows

  • Oracle 11g - Windows

  • or SQL Server 2012 and up

Thanks in advance for any help.