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?
¡Resuelto! Ir a solución.
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
@Tyrion_Lannister
I appreciate the detailed reply - this worked like magic.