Oracle Gallery Data Connection

[tstanch3]

I am attempting to establish an oracle data connection via the Gallery, but continue to receive ORA-12154 error. I have gotten together with the database admin to troubleshoot the Oracle Client and connection on the server and no issues were identified. Are there any tricks to create an Oracle data connection on Gallery? Are there any thoughts on how to establish this connection? What are the best ways to troubleshoot from Gallery?

[Chandler_Bing · Accepted solution]

Hello @tstanch3 

Well this might be one of the workarounds/solutions:

Firstly, make sure you are able to telnet into the Oracle host from your server. open command prompt -> telnet YourOracleHostName 1521. If it fails then the issue is with firewall. You have to open port 1521 for connection

Secondly,check Oracle driver is installed on the machine.

1.Create an ODBC connection on the server, search odbc connections->System DSN-> Add

select the Oracle driver, then give the details and test it. Make sure it is successful

2. Open Alteryx Designer on the server and drag and drop the input tool then click on datasources->Oracle->ODBC then select the connection you have created and hit ok

3. Input tool with visual query builder and sql editor window pops up. just hit ok

4. Now copy the string created on the input tool something like  odbc:DSN=ConnectionName;UID=UserName;PWD=__EncPwd1__

5. Go to gallery data connections -> Add new connection -> Other

Give the connection name as your DSN name and paste the string you have copied from the input tool and click save. Give access to the required user and it should work.If yours is a multi-node environment then DSN should be created on all nodes

If you want to create an OCI connection, then go to Add data connection-> other and paste the string something similar like this

oci:UserName/__EncPwd1__@YourOracleHostName:1521/servicename and hit save, give access to the user and they should be able to use it.

Hope this helps!

cheers

[tstanch3]

@Tyrion_Lannister

I appreciate the detailed reply - this worked like magic.