Difference between Oracle Client and ODAC

Question

What is the difference between installing the full Oracle Client and an Oracle Odac install? Which will I need to do .NET development on my dev workstation and which will I need on the web application server that will connect with the Oracle database on another server?

Sensibility

Answer 1

ODAC includes Oracle Data Provider for .NET, Oracle Developer Tools for Visual Studio (ODT), Oracle Providers for ASP.NET, .NET stored procedure support, as well as additional Oracle data access software for Windows.

-Oracle's site.

The full Oracle client includes a lot of extra software, for example SQLPlus, SQL Developer etc.

Anyways, that's mostly irrelevant. The standard way of obtaining database drivers in .NET is by using NuGet, and Oracle has an official managed driver with no outside dependencies that's the simplest way to get started. You add that package via NuGet to your application, and the necessary DLL's will be included with your application when you publish it without needing to specially configure the server.

Trachytic

We are converting our servers from win 2003 to win 2012 and so can specify what we want for an Oracle server install. In our case we have many websites built as x86 apps and version 2.111.7.20 of the Oracle.DataAccess drivers. The apps do not have local copies of the DLLs. We are planning a full client install on the dev and qa Web App servers - will the latest version of the full client be backward compatible with the older 2.111.7.20 drivers? What version is recommended for the production environment where we don't need sqlplus? – Sensibility

@VinceMiccio Like I said, you should abandon all of that and use the official managed driver. Then there's no x86 vs x64 issues, because it's all .NET code. – Trachytic

Unfortunately it's not an option to make any changes to the 60 or so websites we have to migrate, but we will use the new managed driver for our new sites. So the question remains what to install on the server that will work with both the new managed driver and the 2.111.7.20 driver? – Sensibility

@VinceMiccio Beats me, that wasn't part of the original question. – Trachytic

Ok will wait a bit and see if anyone else responds and then post my followup as a new question if necessary. – Sensibility

@VinceMiccio Nobody is going to answer that here, because that wasn't part of the original question and you already accepted by answer. You'll have to make a new question. Anyways, I think it's a huge mistake not to switch everything to the managed driver. 60 sites or not, it'll be a huge headache to get all the servers configured correctly whereas switching to the managed driver is just removing a couple DLL's, adding a NuGet package, and testing (you do have unit tests right?) to make sure everything still works. – Trachytic

Actually we had the DBAs install Oracle full client as per your recommendation (version 11.2.0.1.0) and it all just worked! No fuss no muss. We will be starting a project to convert to 64 bit drivers, but that's another conversation. Thanks! – Sensibility

Answer 2

It's pretty confusing, and writing this down again helped me.

My summary:

• The Oracle Instant Client only exposes direct OCI (Oracle Call Interface) APIs, so only relevant to Oracle-aware applications (see, say, http://www.oracle.com/technetwork/topics/winx64soft-089540.html )
• ODAC includes the Instant Client, plus also a bunch of Microsoft-facing APIs on top of that (.net providers, ASP.Net membership provider, OLEDB driver, ODBC driver, MTS transaction coordinator) which cover what you'd need in most Windows application scenarios. eg http://www.oracle.com/technetwork/topics/dotnet/downloads/install112021-200037.html. ODAC comes in two flavours: the 'xcopy' version, and the OUI (Oracle Universal Installer) version (and only the OUI version includes SQL*Plus [#1])
• ODT (Oracle Developer Tools for .Net) provides the Visual Studio integration, and is normally bundled with ODAC (you get a download called ODTwithODACxxxx.zip) eg http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
• The Oracle.DataAccess nuget package is a version of the ODAC .net driver, so requires an existing Instant Client install (ie uses OCI). Confusingly, at one point this was labeled a managed driver (because it is, just has some significant non-managed local dependencies).
• The Oracle.ManagedDataAccess nuget package is a fully-managed .net driver that works in 'direct' mode, and needs no local instant client

So for most .net people, 'Oracle Client' means OUI-installed ODAC Instant Client + .Net drivers, possibly also ODT.

For the nugets, it's worth pointing out that even now (Jan 2017) the Oracle.ManagedDataAccess driver still can't do a bunch of stuff[#2], so Oracle.DataAccess + InstantClient is not a totally obsolete option. If you are just reading and writing with SELECT/INSERT, or vanilla ADO.Net you will be fine. I'm told the EF support is much better than it used to be also. DevArt's dotConnect drivers are another (very good) option here.

There's also a nuget package for the Oracle instant client, but I have no idea what that is for. Presumably Win32/.Net native-OCI apps that want a zero-install Instant Client via nuget. Both of them.

[#1] SQL*Plus would presumably be little used at runtime, though there is a separate installer listed on the Instant Client download page if you want to to add it to an existing ODAC install.

[#2] eg: call oracle stored procedures with table-valued UDTs

Fabien

Instead of NuGet You can download ODP.NET (Oracle.ManagedDataAccess and Oracle.DataAccess) drivers also from Oracle page: 64-bit Oracle Data Access Components (ODAC) Downloads – Marji