Forming Oracle database connection using Host, Port, SID

[shaunak_bangale]

Looks like Oracle database connection can be formed through either standard way or TNS server way.

I have the standard parameters: Host, SID, Port

Oracle connection in Alteryx, however, asks for TNS Server Name, Username, Password.

Just by putting Host as TNS Server Name, the connection is not getting formed.

What is the best way to connect using these available parameters?

Which connection has it? I tried some of them and maybe there are called something different and I am not able to recognize it. 

Appreciate your help!

Regards

Shaunak

[dataMack]

If you are using the TNS method, you should have a TNSNAMES.ORA file in the directory where your Oracle driver is installed.  Open that file in your favorite text editor to make sure there is an entry for the Oracle host you are trying to connect to.

If you don't have an entry for the namre you are trying to use, then of course Alteryx won't know what you are trying to connect to.  You can use the following site as reference to add an entry in your tnsnames.ora file:

http://www.orafaq.com/wiki/Tnsnames.ora

[shaunak_bangale]

Thanks Jason I did that- edited the TNSNAMES.ORA file with the required details. Still I am getting this error. What should I put in the TNS Server name? I have host, port, protocol, SID, username, password.

And then I put the user/pwd@dB|<tablename> and I got this error. 

[dataMack · Accepted solution]

In the Alteryx box for TNS name you put the name fron your TNSNAMES.ORA file, not the host and port- that stays in the TNSNAMES.ORA file

So, using the example TNSNAMES entry from the website I linked to in the message above:

ORA11 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11)
 )
)

You would just put 'ORA11' in the Alteryx box for TNS Name and thne your username and password.

You can think of the TNSNAMES.ORA as a mapping file that makes it easy for an end user to connect- all you ever have to remember is 'ORA11' and your credentials.  TNSNAME files are typically managed at the enterprise level and pushed down to users' machines.  That way if the host or port every changes, it won't impact your queries because that file handles the details of where to point when you want to connect to 'ORA11'

[shaunak_bangale]

Awesome! This worked. Thanks Jason. Now I can see the list of tables in the database after I click 'Ok' first time. How do I save this list or browse through it without going through connection-TNS Name-Ok again? The "browse" tool seems to work only for tables and not for a database.

[dataMack]

If your goal is to get the list of tables on the database into your workflow (ex. you want to write the list of table names to a text file or excel file so you can keep track of them) then you can use in input tool agains the same connection, except use a SQL statement like this:

SELECT owner, table_name
  FROM all_tables

Which will return a list of all the tables and the assoicated owner.

Otherwise, Alteryx does save the recent connections, so if you were to drag a new input tool on the canvas and select the same Oracle connection, you could simply click the 'Tables' button on the input tool popup window to see the list of tables on the schema to which you are connecting. 

[patrick_mcauliffe]

Our organization doesn't use TNSNAMES.

I was advised that they prefer us to use a JDBC connection.  The only application they have "approved" of is Oracle SQL Developer. 

Any ideas you have for still using Alteryx to get to Oracle within those constraints?