Alteryx Designer

Definitive answers from Designer experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

How To: Connect to an Oracle Database in Alteryx

Alteryx
Alteryx
Created

 

How To: Connect to an Oracle Database in Alteryx


This article details different methods for connecting to an Oracle database from Alteryx. 

Note: All screenshots included in this article are for Designer version 2019.2 or later.


 

Prerequisites

  • Alteryx Designer ≥ 11.0
  • Oracle
    • Validated on 12c, 12.01.00.01
  • Oracle Instant Client  Driver or an internet connection (if you have never connected to Oracle before)


Connection Types:

 

1.     Quick connect

2.     OCI

3.     ODBC

4.     OleDB

5.     32-bit connections (OCI, ODBC, OleDB)

  idea Skyscrapers

 

 

1. Connection Type: Quick Connect

 

Procedure: If you have NEVER connected to Oracle before

 

a.     Open a new workflow in Alteryx Designer.
 

b. Add an Input tool to the canvas, and select Data Sources from the drop-down menu in the configuration window then select Oracle Quick connect
 

  idea Skyscrapers

 

  idea Skyscrapers

 

Alteryx Designer checks for the client as follows:

        Check for full Client install:

        Check PATH variable for Oracle client path

        Check Oracle home directory for existence of tnsnames.ora file (note: it does not check for content of file and it does not use the file, it just checks for existence)

        If it cannot find tnsnames.ora file, it assumes client install is incomplete and prompts to install instant client from Oracle website

        Check for Instant Client in User Settings

        If Oracle client is not found or it is incomplete, it prompts to install instant client from Oracle website.

 

c.      Select a directory to install the client to.

A window will appear prompting you to select a directory to install the client. Best practice is a to use a  new empty  folder you created for Alteryx to install the driver to :

  idea Skyscrapers

 
d. Alteryx will then download the appropriate client and install it for you. This may take a few minutes.

NOTE: The install directory will be saved with the user settings and will only be visible to the user who installed the client. If you are installing this on a server or as an admin for another user on the machine, you should download the client directly from Oracle  ( Instant Client Downloads for Microsoft Windows (x64) 64-bit ) and follow the installation instructions provided by them at the bottom of the page. 
 
e. Enter information in the database connection window to create a new Oracle Saved Database Connection


  idea Skyscrapers

 Needs: hostname, port, service name, user name, password

 

f.       Test the connection then click OK to connect.

 

g.     Automatically creates a saved data connection
 

  idea Skyscrapers

 

 

Connection string format:

        oci:/__EncPwd1__@:/

 

All information necessary to connect is contained in the connection string itself

 

h.     Going forward, Alteryx will not ask to install the driver again. It will recognize the driver previously installed. If you need to connect to the same database in another input tool, you can find the connection in the drop down option. 
 

idea Skyscrapers

 

 

 

i.       If you need to connect to a different database, you can repeat steps 5 - 8 for the new database.  

 

 

Procedure: If you have connected to Oracle before, or if you want to use an existing Oracle client on your machine

a. Open a new workflow in Alteryx Designer

b. Add an Input tool to the canvas. Select Saved and select the connection

  idea Skyscrapers

 

c.  Alteryx will search your machine for an existing Oracle client.
 

-  If Alteryx  can find an existing client, the Saved Data Connections will appear. 
 

-   If Alteryx  cannot find an existing client, and you have the appropriate permissions on your machine, internet access, don't care if you use an existing driver, and your Oracle DBA did not tell you that you must use a specific client, -> Follow the procedure for users who have never connected before to install the Oracle Instant Client through Alteryx and use it to connect. 
 

  - If Alteryx  cannot  find an existing client, but you have a client you want/must use to connect, most likely Alteryx can't find the existing client because it couldn't find Oracle Home in the Windows Environmental Variables and/or there were no tnsnames.ora file in the Oracle Home Directory. You can force Alteryx to use the existing client by setting the Environmental Variable and creating a tnsnames.ora file  :

 

·        Close all instances of Alteryx.

·        Navigate to the folder containing the Oracle client files.

·        Create a blank text file called tnsnames.ora. Be careful not to save with an extension such as .txt or .csv and save to the folder.

·        Copy the path of the folder.

·        Open up Windows Environmental variables and create a new one called TNS_ADMIN with the path for the Oracle client file folder as the value.

·        Note: If you already have a tnsnames.ora file, or a TNS_ADMIN variable,  do not  overwrite any of these.

·        Open Alteryx and try connecting again.

 

 

2. Connection Type: OCI

 

a.     Open a new workflow in Alteryx Designer
 

b. Add an Input tool to the canvas, and select Data Sources from the drop-down menu in the configuration window then select Oracle OCI.



idea Skyscrapers
 

Enter the TNS Service Name from your tnsnames.ora file
 

        Uses the Oracle Client and the tnsnames.ora file to connect

        Needs the Oracle Instant Client or full blown client installed

        Needs properly formatted tnsnames.ora file. Refer: https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF007

Example Basic Format of tnsnames.ora File

idea Skyscrapers  

        Needs Oracle home directory set in PATH variable

        If tnsnames.ora file is not located in Oracle Home directory, TNS_ADMIN variable needs to contain path.

 

  idea Skyscrapers



3. Connection Type: ODBC
 


To connect via ODBC, first install the ODBC driver on your computer. Then, use the ODBC Data Source Administrator to create a Data Source Name (DSN) for your connection.
 

        Open ODBC Administrator. Click the Drivers tab and verify that the driver appears in the list of ODBC drivers installed on your computer:
 

  idea Skyscrapers


        A DSN must be created through the Windows ODBC Data Source Administrator:

idea Skyscrapers  

        Use Test button to make sure it is working

        Uses the tnsnames.ora file

 

 

·        You can then select the DSN in the Alteryx tool you are using to connect to the database.
 

a.     Open a new workflow in Alteryx Designer
 

b.     Add an Input tool to the canvas, and select Data Sources from the drop-down menu in the configuration window then select Oracle ODBC. You have to enter the User Namd and Password since Oracle doesn’t save it in the ODBC DSN.
  idea Skyscrapers

  idea Skyscrapers

 

4. Connection Type: OleDB
 

To connect via OLEDB, first install the OLEDB driver on your machine.
 

After installing the driver you can then select it from a list of drivers available in the Alteryx tool you are using to connect to the database.
 

a.     Open a new workflow in Alteryx Designer.
 

b.     Add an Input tool to the canvas, and select Data Sources from the drop-down menu in the configuration window then select Oracle OleDB. The "Data Link Properties" window should appears.

 

  idea Skyscrapers
 

 

Select the driver for Oracle and click “Next.” Once you configure it, Click the “Test Connection” button to test that you are set up correctly.

 

  idea Skyscrapers
 

 

Data Source should be the Service Name from your tnsnames.ora file.

idea Skyscrapers
 

  

5.   Connection Type: 32-bit connections (OCI, ODBC, OleDB)

 

        Usually, 32 bit is used because of IT/other company requirement

        32 bit connections are available for OCI w/tnsnames.ora and (ODBC or OleDB) with applicable client installed.

        In order to use these connections, you will need to have the driver already installed and a tnsnames.ora file created in the Oracle Home directory. If you are using ODBC you have to set up a connection through the Windows ODBC Data Sources Administrator before attempting to connect through Alteryx.

        Although it can be done, we do not recommend installing both 64 bit and 32 bit clients on the same machine

        User can work with his IT/DB Admin

        Connection string will indicate 32 bit connection:

32bit:oci:/PASSWORD@

        Sometimes connection will not be established, type in connection string manually in format: 32bit:oci:/PASSWORD@|

        Connection window is identified as 32 bit

 

32-bit connection OCI:

idea Skyscrapers
 

 

32-bit connection ODBC:

idea Skyscrapers
 

 

32-bit connection OleDB:

idea Skyscrapers

 

 

 

Common Issues
 

  • If the driver was installed through Alteryx, the install directory will be saved with the user settings and will only be visible to the user who installed the client. If you are installing this on a server or as an admin for another user on the machine, you should download the client directly from Oracle  ( Instant Client Downloads for Microsoft Windows (x64) 64-bit ) and follow the installation instructions provided by them at the bottom of the page. 
  • If Alteryx is taking a long time to load the driver or cannot find it at all, and you are connecting via the OCI option with a tnsnames.ora file, try setting the  TNS_ADMIN  variable to make sure Alteryx is using the correct client. Also make sure that the first occurrence of an Oracle client in the PATH variable points to the Oracle client Alteryx should be using. 


Common Errors thrown by the database


·        If tnsnames.ora file is not located in Oracle Home directory:  

 
idea Skyscrapers
 

·        If you don’t have TNS_ADMIN variable containing the path:
 

 

idea Skyscrapers  

 

 

 

 

Additional Resources

 

 

 










 

















 















 
Comments
7 - Meteor

Hi @ntobon  , thanks so much for putting together this comprehensive page! might you have an example of how to create an INDBC file for Oracle?  similar to this (which is for MySQL) https://community.alteryx.com/t5/Alteryx-Designer-Discussions/INDBC-File-Microsoft-SQL-Server-Connec... 

 

That way, users can customize the connect and go DSN-less, and account for other local settings and configs with ease?

 

Many thanks. 

Alteryx
Alteryx