Tableau Oracle Connection
by Toby Erkson
I originally wrote this document to help me and my end users after struggling with Oracle connections. While the focus is for a Tableau Server administrator everything here holds true for end users working with Tableau Desktop.
If the Tableau Server is initially being set up for Oracle connectivity then follow the instructions under Initial Setup otherwise you’re going to be adding additional connections so go to Adding Servers below.
A good indicator that something isn’t set up correctly is any of the following Oracle errors:
If Oracle Client (or similar) isn’t installed on the machine then install the appropriate driver for the version you’ll be accessing -- the newest isn’t always best -- and manually create the directory C:\Oracle_Client\network\admin
If you have access to an existing TNSNAMES.ORA file, either from an end user that already has one or one from your dba (Oracle database administrator), then copy it and save the file as TNSNAMES.ORA in the admin folder you created. If you don't have one then you'll need to create the file in a plain text editor like (I like Notepad++).
Here's an example of an entry (names and addresses have been changed to protect the innocent):
# Production EDP1 Database
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 99.999.9.99)
(Port = 1521)))
(CONNECT_DATA = (SID = EDP1)))
Next, update the machine's PATH as described in Step 2 (TNS_Admin environment variable) of the knowledge base article, Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora, and then reboot the server.
If the Tableau Server has already been set up then the only thing needed to do if adding a new Oracle data source is to update the TNSNAMES.ORA file with the server info. Simply follow how other entries are added or, even better, get the TNSNAMES.ORA info for the db from the dba. Oracle is very picky about the format of entries in the file so don't change how it looks.
Accessing the Oracle database
Users now open Tableau Desktop, Connect to Data, On a server, and select Oracle.
In the connection dialog window, "Step 1" would be 99.999.9.99:1521/EDP1.world. If you were to click on the Advanced... button here's how you would enter in the info:
Server Name: 99.999.9.99
Service Name: EDP1.world
Click the OK button.
The other Steps are filled out as necessary; typically a specific username and password will be required.
Validating the drivers and connection
Getting the license version
Executing just tnsping will return the Oracle driver version (ignore the error message):
Testing the drivers: Connecting to a database
Perform a tnsping to a database listed in the tnsnames.ora file. Below is a successful connection to ddcprod.world:
If the db cannot be found:
- insure the tnsnames.ora and sqlnet.ora files are in the appropriate folder
- the TNS_ADMIN environmental variable has been set
- reboot the server.
Wait...what is this sqlnet.ora file? I don't know but if you find it on your machine then copy it and paste it into the same folder as the file. Doing this fixed some connection errors for us.
-- End Of Document --
For additional info see the Knowledge Base article, Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora.