Category Archives: Database

Oracle 11g Client connecting to both 10g and 11g databases

7 down vote accepted

There is commonly no problem to connect to an older Oracle database with a newer client driver, e.g. to connect to a 10g or 11g database with a 12g Client. The rest of the answer belongs to technical problems which can occur in your .Net program in the case that an Oracle Client is (maybe) already installed on the computer executing your program.


Update 2014:

In the meanwhile Oracle has released a managed .Net driver for the Oracle database. So instead of installing a local Oracle Client or delivering an Instant Client along with your app, the preferred way should be to deliver just the managed driver without any dependencies to local configurations. Then you have no trouble with installed clients, the GAC, the Oracle database version and so on. You can download the managed driver from the Oracle website.


Previous answer, still needed if you can’t use the managed driver:

The problems begin if you don’t know if there is an Oracle client installed on your client workstations. If you are talking about GAC I assume, you don’t know if an Oracle Client is installed and if so, which version it is.

If you don’t want to rely on an installed Oracle Client, you can deliver an Oracle Instant Client with your .Net application. For example you can download ODAC 11.2 Release 4 (11.2.0.3.0) with Oracle Developer Tools for Visual Studio, which gives you an Oracle Client installation for your developer workstation (with Visual Studio support for DataSet development and EntityFramework) as well as all files needed for an instant client.

For an instant client you need the following files (search them in the subfolders of your ODAC installation):

  • oci.dll
  • ociw32.dll
  • Oracle.DataAccess.dll
  • orannzsbb11.dll
  • oraociicus11.dll (if you are using english language, else the corresponding .dll)
  • OraOpd11w.dll
  • tnsnames.ora (if you need it)

In addition the following .dll files are needed from your Windows directory:

  • mfc71.dll
  • msvcr71.dll

Just copy all that files in the working directory of your application (where the .exe file is).

Now how belongs that to the GAC?

If an Oracle Client is installed on the client machine there is also an Oracle.DataAccess.dll in the GAC. Also it is possible, that a policy was installed which states something like: Independant of the Oracle.DataAccess.dll your program is referencing, the Oracle.DataAccess.dll version from your GAC shall always be used. If you install the ODAC I linked above, you find that file under

C:\Windows\Microsoft.NET\assembly\GAC_32\Policy.4.112.Oracle.DataAccess\v4.0_4.112.3.0__89b483f429c47342

The result is that your .Net application always throws an exception if you are trying to load the Oracle.DataAccess.dll (with an error message like “The provider is not compatible…”), if the client machine has another version of Oracle Client 11 installed than the one you are referencing in your application.

To solve that, you can configure your app.conf so that the publisher policy will be ignored and always your version is loaded:

<configuration>
...
  <runtime><assemblyBindingxmlns="urn:schemas-microsoft-com:asm.v1"><dependentAssembly><assemblyIdentityname="Oracle.DataAccess"publicKeyToken="89B483F429C47342"/><publisherPolicyapply="no"/></dependentAssembly></assemblyBinding></runtime></configuration>
via:http://stackoverflow.com/questions/9364570/oracle-11g-client-connecting-to-both-10g-and-11g-databases

MSSQL: Remove Diacritic Marks From Characters – Vietnamese unicode to non-unicode in database

Sometimes we want to remove accents from vietnamese words to Latin based characters for search or matching routine in database. However, Microsoft SQL Database does not offer normalized function which we can use to remove accents from words such as:

  1. AI ĐI TRÊN DÀM ĐƯỜNG TRƯỜNG => AI DI TREN DAM DUONG TRUONG
  2. áo em chưa màc một lần => ao em chua mac mot lan

There is  workaround by using CLR function, see example code below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections.Generic;
using System.Globalization;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlString fn_RemoveDiacritics(String str)
  {
    // Put your code here
    return new SqlString(RemoveDiacritics(str));
  }

Continue reading