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

Question

Hi,
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.

TanuHoque · Accepted Answer

I don't know whether Oracle supports case insensitive searches...

having said that...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?)

it seems to me that your this particular problem can be solved with the StandardizedQueries - which is enabled by default. This is a new features starting at 10.2.

With StandardizedQueries enabled, you DO NOT need to build different WHERE clauses depending on the backend RDBMS, instead your query MUST conform to StandardizedQueries specifications, and ArcGIS Server will do the conversion for you.

http://resources.arcgis.com/en/help/main/10.2/index.html#/Supported_SQL_functions_in_ArcGIS_Server/015400000686000000/

e.g. in this case, simply pass in your where-clause as UPPER('field name') LIKE UPPER('%term%')

without worrying about the underlying database.