I am developing an Excel / VBA – Application that connects to an Oracle database (v 11.2.0.4) instance. I need to query a customers table and search by name. Customers names will contain german umlaut chars (äöüÄÖÜ and ß). I issue sql queries like "… where c.custname = 'Müller'".
The behaviour I am observing is that on my dev system all works well as well as on several users' systems. On few other systems, the application fails with "no customers found", my own error message if the name lookup mentioned before fails (which should not happen as it works at another system).
Further investigation using the PL/SQL Developer tool revealed that on the affected systems the names for customers are returned without umlaut. "Müller" becomes "Muller". However, querying for "Muller" doesn't return anything – so "Müller" is really converted to "Mu[unprintable char]ller" – I guess.
I am quite familiar with Microsoft SQL Server, Versions, Languages, Drivers… but for Oracle I have no experience at all. Can someone advice where / how to check what driver version is installed on a system, how it is configured (NLS-settings?) and how to make sure all systems are set up the same, in this aspect?
Is there a way to totally clean a system of all oracle related drivers, files and registry settings to get a clear start and have only one, the most recent driver, available?
Further information (to make things worse):
-
My VBA-macro uses a base class which is in use "forever" in my company. It connects through OLEDB and builds it's connection string using "Provider=msdaora.1" meaning it is using the Microsoft provided Oracle driver intended for Oracle around version 7 (?).
-
When using PL/SQL Developer to test, it takes a "real" oracle driver installed. For my system, there is a choice from "10.2.0/client_1", a "12.2.0/client" and "12.2.0/client_2"
I am going to try and compare my Microsoft Oracle driver to the non-working systems, just to see what it is.
I will try to upgrade the base class to stop using the obsolete Microsoft driver and use the recent oracle driver. However, manual queries using PL/SQL Developer showed the same basic problem… therefore I am not sure I will solve the issues.
I'd appreciate any idea where to look, what to remove or install or any other idea!
Best Answer
Indeed, the OLEDB provider from Mircrosoft is deprecated for ages and you should not use it anymore. Certainly it can have problems when you work with Unicode.
Use the OLEDB Provider from Oracle. After installing it, it should be sufficient to modify just the connection string from
connectString = "Provider=MSDAORA;Data Source=...toconnectString = "Provider=OraOLEDB.Oracle;Data Source=....OLEDB Provider from Oracle always use UTF-16 (see OraOLEDB Provider Specific Features), so german "Umlaute" should be no problem.
Note provider
msdaoraalso uses local Oracle Client installation (likeOraOLEDB.Oracle), you cannot use it without any Oracle Client installation.In order to verify properly what is stored at database level try