We get a lot of questions from clients about how to get up and running quickly with an Oracle client so that they can get connected to our Express Data Hub.
The quickest way is to use the Oracle Instant client. If you would like to install the full client, skip to the full client install instructions below.
These videos cover most of the Instant Client steps:
Step 1 – Install the instant client
Step 2 – Configure the TNSNAMES file for Oracle
These videos cover all the steps except for the Oracle ODBC driver setup. The instructions below cover the entire process including the ODBC steps.
Here are the steps for installing the Oracle 12 Client
1. If you are using it with Excel you need to determine if your version is the 32 bit or 64 bit version. In Excel 2016, you can find this from the File – Account menu and click the About Excel tile. The version will be right at the top of the Window.
The Oracle 12 client installer will prompt you for the connection settings and set up the tnsnames.ora file for you if one doesn’t already exist. You can use EXPRESS-HUB as the alias name, 22.214.171.124 as the host and EXRESS as the service name if you would like to set up a connection for the Express Hub using Open VPN.
The same thing applies to Toad. Check to see which version you are using and install the appropriate Oracle Client package. If you would like to use Free Toad, you can get it here.
2. Unzip the package, open the folder and run setup.exe. It may take a few minutes for the installer to open.
3. Select the language – English is the default
4. Specify the Windows account. We suggest the “Use Windows Build in Account” option which is the default.
5. Specify the Oracle base folder. The software location will update automatically when you change the base folder.
6. Select the components to install. You can unselect the Visual Studio options unless you plan to work with Oracle in Visual Studio.
7. Click Install to run the installation.
8. When the installer is done, copy the tnsnames.ora file into the …\product\12.1.0\client_1\Network\Admin folder.
9. Navigate to the C:\Oracle_Instant_Client folder, right click on the odbc_install.exe and Run as
10. If you have more than one Oracle client installed – for example both the 64 and 32 bit versions, you can force them to use the same tnsnames.ora file by creating a new environment variable called TNS_ADMIN to tell the system where to find the Oracle client and tnsnames file. Make a note of the path to the folder where you installed the file so you can use it to set up your environment variables. We used C:\app\client64\product\12.1.0\client_1\Network\Admin for our TNS names.
The environment variable setup is done in the Windows System properties. In Win 7 you get at this by right clicking on My Computer and select Properties. In Win 10, right click on This PC and select properties. Next click on Advanced System Settings to bring up the System Properties window:
Click on the Environment Variables button to bring up the Environment Variables window.
To create the TNS_ADMIN environment variable, click New in the Environment Variables under the System Variables section, fill it in like the window below with your tnsnames path for the variable value and click OK.
Testing the Connection
If you are using Toad, you can test the connection from within Toad by following the instructions for connecting with tnsnames in the second video above to make the connection.
If you are using Excel, you will need to configure an ODBC data source.
We can test the connection by setting up a new ODBC DSN using the Oracle driver that was just installed. This is done with the ODBC Data Source Administrator tool and you need to be aware that there is both a 32 bit version and a 64 bit version of this tool so be sure to use the one that matches the Oracle Client that you just installed. Just to make it more interesting, they are both called ‘odbcad32.exe’ but you can tell which one to use by which Windows folder the executable is located in.
The 32 bit version is typically found in the C:\WINDOWS\syswow64 folder and the 64 bit version is found in the C:\WINDOWS\system32 folder. It may sound counter intuitive but that’s not a typo.
Here are the steps to set up the DSN and test the connection:
1. Open the ODBC Admin tool, click on the System DSN tab and click Add. This will bring up a list of installed ODBC drivers. Select the Oracle driver from the list. (Hint: If it’s not on the list, you are either using the wrong version of ODBC Admin or you may have missed Step 7 above.)
2 Fill out the Oracle ODBC Driver Configuration Screen:
a) Data Source Name – Use EXPRESS_WELLS
b) Description – Optional
c) TNS Service Name – Use the drop down arrow to select EXPRESS_HUB_VPN if you are using an Open VPN connection or EXPRESS-HUB.CWDINC.CA if you have a direct connection. If there is nothing on the list, it means that the TNS_ADMIN environment variable is not set up and ODBC can’t find the location of the tnsnames.ora file.
d) User ID – the user name for the Oracle database that you were provided by CWD
The configuration screen will look like this but with your own user ID.
If you are using the VPN connection, this is where you should make sure that your Open VPN connection is installed and connected.
Now, click the Test Connection button and enter the password you were provided by CWD. If all is well you will see the “Connection Successful” message and you are done and ready to connect to the database with Excel or any other ODBC enabled application.