Escape and Oracle 9i

From: Jeroen ter Hofstede <jeroen.ter.hofstede_at_wanadoo.nl>
Date: 22 Nov 2004 01:28:15 -0800
Message-ID: <483a5c5.0411220128.632df49b@posting.google.com>

Our application runs on multiple databases, including Oracle 8i, using ODBC. One of the SQL-statements it uses is of the form

select * from foo where bar like '%baz%' escape '|'

The sting is in the escape-clause. One of our customers now tries to run our application agains an Oracle 9i database, which complains about this statement:

"[Oracle][ODBC][Ora]ORA-01425: escape character must be character string of length 1"

As far as I can see is '|' just a single character. Some experimentation with interactively issues SQL-statements shows that the choice for the '|' character is not the culprit. Instead, Oracle can be made completely happy by using '||' as escape string.

I suspect some form of character counting problem, perhaps something around expectations for Unicode characters. I cannot find any settings that might be involved in this, nor can I find any references on the Net.

I would be very grateful if anyone has a clue, and is willing to share...

Thanks in advance,
Jeroen Received on Mon Nov 22 2004 - 03:28:15 CST