SQL & PL/SQL

Hi!

I have encountered a strange behavior of the Oracle’s command interpreter.

Given the following select statement:

SELECT DISTINCT PAT.ID
FROM PAT
WHERE
(lower(PAT.PATNAME) LIKE lower(:param0) ESCAPE '\' )

:param = 'Condition\_With\_Underlines\_123^'

The statement is syntactically correct, and usually works fine, but on an exceptional system the Oracle throws “ORA-01424: missing or illegal character following the escape character”.
Which is very strange, as the escape char is followed by “_”, so it should be okay.

The error encountered via ADO.Net (OracleClient), but reproducible with SQL Developer also.

-if I change the statement, and explicitly set the same value (no param used) no exception thrown!
-if I change the escape char to a different character and update the value of the param accordingly no exception thrown!
-the error is encountered only on one system. Several other system (same installation, same OS, same Oracle) has no issue with this select statement.

OS: Win7 64bit

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

NLS settings:
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Do somebody have any idea what Oracle/Windows setting can have similar side effect? I am suspecting some kind of regional setting or something similar…
I can simply change the escape char to a different one, but what is the warranty that the error would not occur again with that escape char on a “crazy” system somewhen?

Edited by: user9365665 on 2010.03.19. 11:20

Edited by: user9365665 on 2010.03.19. 11:20