Single quote as first character in string fails in compare

From: Joachim Verhagen <Joachim.Verhagen_at_xs4all.nl>
Date: Fri, 07 Oct 2011 20:30:38 +0200
Message-ID: <orgu87htc7mlo50errprb9qpagslqlaeub_at_4ax.com>


I got a very strange problem. If the first character in a string is a single quote I get a syntax error, but that only happens in a compare and not in sqlplus.

This is what happens:

SELECT * FROM dual WHERE dummy ='''aaaa'

DBArecordSet.OpenSet: Error from: 'OraOLEDB' 0x80040E14 (-2147217900) ORA-00933: SQL command not properly ended Native: 933 (help=.0)

This goes wrong with an OLEDB and ODBC connection. It works in sqlplus.

The following all works:
SELECT '''a''' FROM dual

SELECT * FROM dual WHERE dummy like '''a'

SELECT * FROM dual WHERE dummy =N'''aaaa'  

I tried this with Oracle 11.2 and Oracle 10.2 server and Oracle 11.2 and 10.2 drivers for OLEDB

 In the Netherlands some names start with '. It does not happen often, so we did not notice this before and have no idea where in our program this might occur.

Does anybody know this problem and a solution?

Joachim. Received on Fri Oct 07 2011 - 13:30:38 CDT

Original text of this message