Introduction to database connections in Excel for efficient risk control - Part 3

Part 3: Database connections in Excel – Tricks with Oracle

In the article “Introduction to database connections in Excel for efficient risk control”we have already mentioned about how to create a connection from Excel to the Oracle database (see “Oracle For the SQL user”).

First of all, you need to create an ODBC connection to the database in Windows. In this step, difficulties usually arise, because this process has some tricks.

So in this article we will tell you in more detail about the process of creating an ODBC connection to Oracle database in Windows.

First, you need to install on your computer Oracle client and Oracle ODBC driver. Best suited for this is Oracle Data Access Components (ODAC) for Windows, that includes the Oracle client and driver to communicate with different systems.

A list of the various versions of ODAC available for download can be found here - http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html.

The first question that arises is: Which version to choose – 32-bit or 64-bit?

Generally, you choose ODBC driver version based on version of the tool that you use to get data from Oracle database. We need to get data from Oracle to Excel. So, we should choose the same version as MS Excel. If you don’t know the version of your Excel, please follow to this link - https://support.office.com/en-ie/article/What-version-of-Office-am-I-using-932788b8-a3ce-44bf-bb09-e334518b8b19.

Second question – ODAC for which database version to choose?

The logical approach is to choose the ODAC for the same version of Oracle that is installed on the main database server.That is, if Oracle 11 is running on the server, then ODAC should be chosen for Oracle 11. Otherwise, the installed client and driver will not be able to interact with the server.

The third important point

On the website, there is access to many different builds of ODAC. And it is necessary to check that the file we have about to download contains the necessary components – Oracle Instant Client and Oracle ODBC driver (see Download Includes), the presence or absence of other components is not critical.

And so, finally, we have identified which file we need. Now you need to download it. Download of ODAC is free, but before that, you need to register on Oracle's site, which will not take much time.

After installation, you must edit the system environment variables. To do this, select My computer->System properties->Advanced system settings->Advanced and select Environment Variables

You need to add to the PATH environment variable the directory in which you installed the Oracle client (this is usually something like- c:\oracle\11.2.0\CLIENT). To do this, find the PATH variable in the list and click Edit:

Then click New... and add the TNS_ADMIN environment variable indicating the path to the tnsnames.ora file (usually it is like c:\oracle\11.2.0\CLIENT\network\admin):

In this case, you must edit the tnsnames.ora so that it contains information about the address of the database server.

Add the following entry in your TNSNAMES.ORA file and change the values shown in brackets to suit your environment:

<addressname> =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))

)

(CONNECT_DATA =

(SERVICE_NAME = <service_name>)

)

)

Here is a completed example:

ORA11 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORA12)

)

)

Or you can ask your database administrator for actual version of this file and then replace file on your computer to actual version.

So, right now, we can configure ODBC connection: choose Control Panel, then Administrative Tools, then Data Sources (ODBC), then System DNS

and then Add, on the list, choose Oracle ODBC driver (name can be little bit different), then Finish

and then in the configuration window… wait. You don’t have Oracle ODBC driver on the list? That’s the trick – ODBC driver might not appear in ODBC data sources. This can happen because you use 64-bit Windows but installed 32-bit version of ODBC driver (because you have 32-bit version of Excel). If it is like this, you’ll need to use another ODBC administration panel – run odbcad32.exe from c:\windows\SysWOW64.

So, on the list, choose Oracle ODBC driver, then Finish and then in the configuration window add proper data in the Data Source Name, Description, TNS service name (from tnsnames.ora) and User ID:

Then, you can test connection and when everything is correct, save the connection, close ODBC window.

To create ODBC connection in excel:

Open excel -> Data -> From Other Sources -> From Data Connection Wizard ->ODBC DSN -> Choose our Data Source Name -> Answer to other system questions -> Finish.

And now enjoy your Oracle data in MS Office tools.