Download Oracle Data Access Components

[Liliane Hubright]

Whendeinstalling, use the installation type (i.e. Oracle Universal Installer, xcopy, NuGet, and MSI) that you originally installed the Oracle Home with. You should not deinstall from an Oracle Home created by another installation type. Oracle highly recommends deinstallations be executed with the same version that installed ODAC and for ODAC to be deinstalled first ahead of other Oracle components installed on the Oracle Home.

The above command deinstalls and unconfigures the ODAC components. However, this approach of uninstalling ODAC will leave some Oracle Client binaries and any user generated files in the Oracle Home. To completely remove all the files and folders within the Oracle Home, one can run the following Windows command in addition to the first command:

Note: The Oracle Home files will not be recoverable once they are deleted. Please back up anything important from the Oracle Home before executing the above command. If you wish to run this same command silently, you can execute the following instead:

Automatic Setup

When installing ODAC in a new Oracle Home, OUI automatically copies the Oracle local naming (tnsnames.ora), profile (sqlnet.ora), and directory (ldap.ora) parameter files and settings from an existing Oracle Home into the newly installed ODAC home, as long as they share the same bitness (i.e. They are both 32-bit installations or they are both 64-bit installations.)

Alternatively, existing *.ora files can be copied over from another existing Oracle Home, besides the last active one, to the new ODAC Oracle Home. OUI provides location information for these files from up to three other existing Oracle Homes if they exist. The *.ora files can be customized if the new Oracle Home uses a different configuration from the previous Oracle Home from which the files were copied over.

If installing onto a machine without any previous Oracle Homes present, OUI will ask the user for the database connection alias information. OUI will then automatically create the tnsnames.ora file. If no alias information is provided, no tnsnames.ora file will be created. Even if the user doesn't have all the database connection information readily available, Oracle recommends inserting placeholder values during the install process, then modifying the tnsnames.ora file later with actual values to replace the placeholders later.

Manual Setup

Two of the most common methods for connecting an Oracle client to a database are EZCONNECT and TNSNAMES. EZCONNECT is the easiest to setup. TNSNAMES is much more maintainable in the long term. If you are new to Oracle, we recommend you use EZCONNECT. You only have to choose one or the other to connect.

Note: In the setup instructions below, ORACLE_HOME represents the directory where the Oracle client Home was installed on your machine. A typical directory for an OUI Oracle client Home is:

C:\app\client\\product\19.0.0\client_1

1. Copy the sqlnet.ora file located in the following directory:

ORACLE_HOME\Network\Admin\Sample\

to this directory:

ORACLE_HOME\Network\Admin\

This file tells the Oracle client by what methods (e.g. EZCONNECT) Oracle client can connect to the Oracle database server.

2. The Oracle client must then specify a valid user name, password, and data source to connect to the database server. To specify a data source, you can use the EZCONNECT format. In the ODP.NET data source attribute, use the following format to define how the client connects to the database server:

[//]host[:port][/service_name]

An Oracle Net service name allows the Oracle client to use a simple alias to connect to the database server. The alias definition contains all the information needed to create a connection to the database server. Alias information is stored in the tnsnames.ora file typically located in the ORACLE_HOME\Network\Admin directory. This alias is used as the data source value in your connection string. ODAC installations do not create a tnsnames.ora file so you need to create one. The following instructions assume you will have to create a new tnsnames.ora file.

The data source alias, hostname/IP, port, and database service name in the tnsnames.ora should be modified appropriately. If the TNS entries in the ORACLE_HOME\network\admin\tnsnames.ora file are not recognized by the Oracle client, create a ORACLE_HOME\oracle.key file using any text editor and type in the following line:

where is the registry key for the ODAC Oracle Home. For example, if the default Oracle Home location was used during the install, the proper entry for the oracle.key file would be the following: SOFTWARE\ORACLE\KEY_OraClient19c_home1

Machine-wide configuration is no longer supported beginning with ODAC 18c. Administrators can still place ODP.NET in the GAC and add the configuration section handler and DbProviderFactory information to machine.config manually if they wish to override ODP.NET settings for individual applications.

ODAC does not support installing into an existing Oracle Server Home. ODAC can be installed into an existing Oracle Client Home if they share the first two major versions for 12.2 releases and earlier (i.e. ODAC 12.2 cannot be installed into an ODAC 12.1 home) or if they share the major version for Oracle 18 and later (i.e. Oracle 18 cannot be installed into an Oracle 19 home). When installing into an existing Oracle Client Home, the same installer technology must be used. OUI ODAC can only be installed into an OUI ODAC home; MSI ODAC into an MSI ODAC home; and xcopy ODAC into and xcopy ODAC home.

Check whether oci.dll in the existing ORACLE_HOME directory has been removed during the uninstall process. If oci.dll was not removed, follow these steps to remove the DLL.

a) Rename oci.dll to oci.dll.delete.

b) Reboot your machine.

c) Delete oci.dll.delete, which should no longer be in use after rebooting.

d) Install the new ODAC.

The PDF and HTML documentation can be viewed from the ORACLE_HOME\ODACDoc\DocumentationLibrary\welcome.html page. To view this page, go to Start Menu --> Oracle - --> Application Development --> Oracle Data Access Components Documentation.

Power BI Desktop needs the Oracle data providers installed at the machine level. After ODAC 12.2, Oracle removed this option from the install, which means out of the box Oracle 18.x and 19.x ODAC installs are not seen by Power BI Desktop. I've spent the last 24 hours installing and uninstalling various versions of ODAC trying to get them to work with Power BI Desktop.

I'm limiting this to the 64bit install (Because that is what I have) and .NET 4.0, becuase that is what Power BI Desktop needs.

I can now report I have ODAC 19.3 working with Power BI Desktop (May 2021). This is thanks to Google and a lot of digging around. I've put a link to the original article I found that helped me get the data providers installed, as this was not my own work! (Power BI Github comment ), I'm just documenting it here for others.

Check your Windows environment variables. Is the Oracle client you are trying to use the first oracle path in the PATH variable? Do you have TNS_ADMIN defined and pointing to a valid tnsnames.ora directory?

After all the hours spent trying to solve this issue it was my companies rolled out version of PBI Desktop. It was right in the article I had read 100 times but skimmed over because I have PBI desktop pushed to my machine. I had to have IT remote in to install.

If you downloaded Power BI Desktop from the Microsoft Store, you might be unable to connect to Oracle databases because of an Oracle driver issue. If you encounter this issue, the error message returned is: Object reference not set. This is an issue with how Oracle driver works in Windows UWP Apps. To address the issue, you have to do the following:

Oracle Data Access Components (ODAC) is a library of components that provides nativeconnectivity to Oracle from Delphi, C++Builder, Lazarus (and Free Pascal) for 32-bitand 64-bit Windows, macOS, and Linux platforms. The ODAC library isdesigned to help programmers develop faster, cleaner and more native Oracle databaseapplications. ODAC, a high-performance and feature-rich Oracle connectivity solution,is an efficient native alternative to the Borland Database Engine (BDE) and standarddbExpress driver. It provides both possibility of connection to Oracle by meansof native Oracle data access and direct access to Oracle without Oracle Client.

ODAC is a direct database connectivity wrapper built specifically for the Oracleserver. ODAC offers wide coverage of the Oracle feature set, supports both Clientand Direct connection modes, and emphasizes optimized data access strategies.

By providing access to the most advanced database functionality, ODAC allows developersto harness the full capabilities of the Oracle and optimize their database applications.ODAC stands out as the set of components with the widest support of Oracle functionality.It is the only component to support Oracle distributed transactions and implementssupport for controllingstatement caching,OCI pooling, and Oracle Advanced Queuing. View the full list of supportedOracle features in Features.

ODAC offers two connection modes to the Oracle server: connection through the OracleCall Interface and direct connection over TCP/IP. ODAC-based database applicationsare easy to deploy, do not require installation of other data provider layers (suchas BDE), and tend to be faster than those that use standard data connectivity solutions.See the How does ODAC Worksection.

ODAC has extra components designed to simplify some tasks and support Oracle-specifictechnologies. Particularly, OraScript serves to execute series of SQL statements,OraLoader serves to load external data into Oracle databases, OraeAlerter and OraPipetransfer messages and data between connections or client applications, and so on.ODAC includes set of classes designed to work with Oracle Advanced Queuing technology.

3a8082e126