community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Connect to an Oracle Database in Alteryx

Alteryx
Alteryx
Created on

How To: Connect to an Oracle Database in Alteryx

 

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

 

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)

 

Procedure: If you have NEVER connected to Oracle before

 

  1. Open a new workflow in Alteryx Designer
  2. Add an Input tool to the canvas, and select Oracle from the drop-down menu in the configuration window.

    2-14-2017 4-57-29 PM.png

  3. 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.

      2-14-2017 5-01-48 PM.png
  4. Alteryx will then download the appropriate client and install it for you. This may take a few minutes.
  5. Enter information in the database connection window to create a new Oracle Saved Database Connection:
    2-14-2017 5-07-33 PM.png
  6. Type in the Connection Name box to create a name to identify the connection. 

    • Your Oracle DB Admin will have to provide the Host, Service Name and Credentials. 

  7. Test the connection then click OK to connect.

    • Errors that start with ORA- and a number, such as the one below, are thrown by the database.

      2-14-2017 5-15-54 PM.png

    • Verify that your connection information (host, service name, port, username, password) are correct and that you have access to the Oracle database. 

  8. If your connection test was successful, click OK to connect to the database and create your query using the Visual Query Builder, Tables Tab or SQL Editor.
    • Note: Stored Procedures can be accessed via the pre/post SQL options in the Input and Output tools for Oracle as of version 2018.3.4.51585
  9. 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. 

    2-14-2017 5-11-10 PM.png

  10. 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

 

  1. Open a new workflow in Alteryx Designer
  2. Add an Input tool to the canvas, and select Oracle from the drop-down menu in the configuration window.

    2-14-2017 4-57-29 PM.png
  3. Alteryx will search your machine for an existing Oracle client. 
    1. If Alteryx can find an existing client, the Oracle Database Connection window will appear. 

      2-14-2017 5-11-10 PM.png

The drop down will reveal Aliases (now called Saved Data Connections) created in previous versions of Alteryx. You can also start typing in the Connection Name window to create a new Saved Data Connection for Oracle.
NOTE: In order for this option to work with an existing client, you must include the EZCONNECT option in the NAMES.DIRECTORY_PATH parameter in the SQLNET.ORA file. 


2. 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. 

 

2-14-2017 5-01-48 PM.png

 

Note: This will ignore any clients previously installed on the computer and it should not interfere with them. This is a good option to use if you are currently using a 32-bit client and want to use the 64-bit client for Alteryx. 

 

3. 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. 

1. Close all instances of Alteryx.

2. Navigate to the folder containing the Oracle client files.

3. 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.

4. Copy the path of the folder.

5. 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.

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

6. Open Alteryx and try connecting again.

 

 

Procedure: If you want to connect to Oracle using a tnsnames.ora file

 

The new Oracle connection uses the Oracle instant client but it doesn't use any tnsnames.ora files you might have on your computer. If you have multiple entries in the tnsnames.ora file, you would have to create a new Saved Data Connection for each of those entries. You can also use the tnsnames.ora file.

 

  1. In the Input tool, navigate to Other Databases and select Oracle OCI (Oracle Call Interface).

    2-14-2017 5-46-20 PM.png

  2. This will display the screen familiar from pre-11.0 versions of Alteryx allowing you to enter the TNS Service Name from your tnsnames.ora file. 
    • Note: if you set up Aliases in previous versions using the Oracle OCI connection, they will continue to function in Alteryx 11.0 as they did in Alteryx 10.6 unless you edit the connection. 

 

Procedure: If you must use a 32-bit driver

 

  1. You will have to navigate to Other Databases > 32-bit Database Connections and then select ODBC or OCI as appropriate. 

    2-17-2017 2-03-30 PM.png

  2. 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. 
  3. To connect using the Oracle Call Interface (OCI), enter the TNS Service Name as it appears in your tnsnames.ora file and the username and password: 

    2-17-2017 2-19-01 PM.png
  4. If Alteryx cannot locate the driver, see the procedure for Alteryx cannot find an existing client but you have a client you want/must use to connect above.

 

 

Additional Resources

 

Comments
Alteryx Partner

Hi, 

 

I am using the 32 bit driver connection - Is there anyway I can save that connection for Alteryx to pick later? As of now, even if I save it as an ODBC connection, it goes directly to the 64 bit ODBC connection screen. 

Alteryx
Alteryx

@dnoted

 

Yes, you can save a data connection to be used again later. 

There is another post on how to manage data connections including a video you can find here

Atom

 

Alteryx
Alteryx

Hi @ranjith

For a 32 bit connection, you have to select "other" in the connection type. Then click on the drop down and select 32 bit and then chose between ODBC, OleDB, and OCI. 

 

 

Meteoroid

Hi, I'm following the instructions in 2C: 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. Is the Oracle client file the folder where the file tnsnames.ora is found? Or is it a folder "higher" up?

 

This is what my path looks like: C:\orant\Ora12\Network\Admin\tnsnames.ora

 

What's the folder "value" that I should?

Alteryx
Alteryx

@emedal63

The folder to use for the TNS_ADMIN variable is the folder that contains the tnsnames.ora file. In your example, it would be C:\orant\Ora12\Network\Admin\

Atom

@

 

I am having some issues connecting to oracle. I am using 64 bit version of ALteryx 11.0.5.26351 and using 32 bit oracle client. I have oracle client installed on my machine and the tnsping shows the below result.

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-SEP-2
017 10:08:22

Copyright (c) 1997, 2010, Oracle. All rights reserved.

TNS-03502: Message 3502 not found; No message file for product=NETWORK, facility
=TNS

 

Environment Variables:

 

ORACLE_HOME: C:\Program Files (x86)\Oracle 11g\11g\bin

PATH: C:\Program Files (x86)\Oracle 11g\11g\bin

 

 sqlnet.ora:

 

NAMES.DIRECTORY_PATH = (LDAP,tnsnames)

 

tnsnames.ora:

 

PFS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=########)(PORT=1521))(CONNECT_DATA=(SID=PFS)))

 

I am using the Oracle Call Interface (OCI), entered the TNS Service Name as it appears in tnsnames.ora file and the username and password, but looks like its not able to find the connection.  ALteryx shows empty input with exclaimation mark.

Can you please help me on this?

 

Alteryx
Alteryx

@ranjith

 

If TNS ping throws an error, it is related to your Oracle client install, not Alteryx. I recommend searching Oracle support/help for the error and possible solutions. 

 

Thank you. 

Meteoroid

Very helpful, thank you.

 

One question, I have Oracle 11 and Oracle 12 clients on my machine. With Toad, I can toggle between either client's current home, but it appears that is not a feature in Alteryx. So now (yesterday 12 was installed) I am unable to execute ETL jobs off my machine or server because Alteryx gets confused. Currently, the only remedy I can think of is having 11 client uninstalled, which can cause dependency issues if I need it in the future.

 

Snapshot off error thrown attached

 

Untitled.jpg

Alteryx
Alteryx

@David-UHG

Alteryx doesn't support toggling between different versions of the client the way you describe doing it in toad. 

Feel free to submit this as a product idea on our forum though! 

If you use Oracle ODBC to connect through Alteryx, you can specify the client version through the Windows ODBC manager and create two different DSNs to use with the two different drivers. 

 

 

Asteroid

Hi, 

 

This article is very helpful! 

 

So, i have Alteryx 64-bit on my machine and i’m tryingn to connect to Oracle 32 bit (Other Databases > 32 bit database connections > Oracle OCI). 

On selecting this, i enter the TNS server name, user name & password. But, nothing happens on clicking OK. No further screen or popup comes up. 

I have the database access i’m tryinn to connect to as i extract data from toad using the same. So, no question about that. 

I also have tns.ora file (C:\Oracle64\product\12.1.0\client_1\network\admin\sample\tnsnames.ora) on my machine. 

 

Please help me how do i connect to the database. I have tried everything from restarting my system to restartng alteryx. 

However, it works absolutely well using same steps on my peer’s machine in the same organization.

 

thanks

Asteroid

 @HenrietteH

 After a lot of restarting Alteryx. I am now able to move to the next window. But, now when i enter the query and try testing the query i get an error “unable to fnd dll: OCI.dll”. 

 

How do i resolve this? Please help me connect to the 32 bit oracle client via 64 bit alteryx 

 

Any help appreciated! Thanks 

 

Alteryx
Alteryx

Hi @Bhavika

 

Since this works for your co-worker, and looking at the error “unable to fnd dll: OCI.dll”, it sounds like this is a problem with your Oracle client install. 

Alteryx Certified Partner

@HenrietteH, this is awesome info!