0

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!

Improve this question
4
  • Do you have NLS_LANG set?
    – Philᵀᴹ
    Commented Sep 28, 2017 at 9:52
  • How would I check? I started a command prompt, used "set >log.txt" and inspected the resulting file for anything with NLS -> no matches. If that is the way / place to check then no, no NLS_LANG set on my system (but working perfect)
    – Ralf
    Commented Sep 28, 2017 at 12:22
  • Correction: RegEdit is the place to check. oracle.com/technetwork/database/database-technologies/… list the place to look into (for drivers 7-10...). I checked two "KEY_OraClient12..." folders, and none contained an NLS_LANG setting.
    – Ralf
    Commented Sep 28, 2017 at 12:31
  • Another bit of information: For my system, an NLS_LANG setting exists one level above the KEY_OraClient level (HKLM/Software/Oracle). My system responds to @.[%NLS_LANG%] with a reasonable answer. My colleagues system did not have this key set, and did not respond to the @... query. After setting the key it now does give the same response to @. but still fails on the real query against the db, still making ü to u.
    – Ralf
    Commented Sep 28, 2017 at 12:57

1 Answer 1

Reset to default
1

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=... to connectString = "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 msdaora also uses local Oracle Client installation (like OraOLEDB.Oracle), you cannot use it without any Oracle Client installation.

In order to verify properly what is stored at database level try

select DUMP(c.custname, 1016) FROM ...
Improve this answer
2
  • Thanks, I changed the connection string as suggested. On the systems it worked before it still works, on the ones that did fail with wrong chars for umlaute this change does not even connect to a db anymore and generates a message along the lines "connection provider not found".
    – Ralf
    Commented Sep 28, 2017 at 12:25
  • Did you install the Oracle OLEDB provider? oracle.com/technetwork/topics/dotnet/downloads/…
    – Wernfried Domscheit
    Commented Sep 28, 2017 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.