Hoping for some help with this - very simple SELECT * from a table in Oracle.
We have two Oracle databases - one I connect to regularly through Alteryx with no issues (although it did take some intensive legwork to make the 32 bit connection work)
I have attached a number of screen shots.
The error I get is OCILogon2 Error: ORA-12170: TNS:Connect Timeout Occurred
I included a screenshot from Oracle SQL Developer showing the query and results
Two screens from Oracle SQL Developer with connection info - SCPO is the connection name that is NOT working. It has an SID instead of Service Name - not sure why or if that matters. I researched and from what I can find from Oracle they are interchangeable... screenshot from tnsnames.ora file shows both connections, similarly configured there.
Also, screenshots of both configurations in Alteryx.
From what I can tell, my configuration is good - but it won't connect to one (SCPO), but will connect to the other(XIW).
I have tried both 32 bit and 64 bit connection setting, but so far am unable to find a resolution to this.
Has anyone run into something like this?
Any thoughts?
Thanks
Mike
解決済! 解決策の投稿を見る。
@michael_heitz I think you flipped the hostname and SID for SCPO when you setup the connection in alteryx. Let me know if you need further assistance.
I have tried it both ways... I also included the screenshot from Oracle SQL Developer so it connects that way normally. I thought the same as you and when trying it reversed still had issues... the error then says:
ORA-12154: TNS: COULD NOT RESOLVE THE CONNECT IDENTIFIER SPECIFIED.
So, I am certain (well, 99.999%) that the config is correct... :(
thanks for your assist Patrick... once I used the IP address instead of the host name in the Alteryx config settings, it successfully connected!
@michael_heitz Glad you got it working! For anybody else who stumbles upon this, here are some notes I had:
Alteryx's connection string looks something like this for 32 bit connections (you just delete the whole 32bit: part for 64 bit connecitons)
32bit:oci:username/password@Host:port/Service_Name
using the above method, the tnsnames.ora file isn't used since all of the necessary items are included here.
If you wanted to use your tnsnames file, you would instead do something like this:
32bit:oci:username/password@Name_From_TNSFile
If you receive something like the ORA-12170: Connection timeout error, that is an indication that there is a problem connecting to your host. Other errors like ORA-12154 indicate that you've connected to the host correctly but now there is an issue with the service name. And as in this case, you may consider using the IP instead of the host name.
Hi Patrick,
Thank you for providing guidance here.
I am also trying to configure Oracle connection with Alteryx and after following this blog and another one( https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Simplest-way-to-Connect-to-Oracle-data...) , I followed the below steps:
After doing all the above steps, I can access the database and pick the table to my input but when I am running the workflow it is giving error "TNS could not resolve the connect identifier specified ...".
I have used host name and I do see you have suggested to use IP address for this kind of error, I will try that and update the outcomes, though just would like to know if that's only the issue here?
Screen shots attached for reference.
Thanks,
Pankaj
1. Edit system variable (TNS_ADMIN) path so it points to 64 bit driver location where tnsnames.ora resides):
https://www.youtube.com/watch?v=cZDDI9HFBIU
2. Add above path to 'Path' variable also:
3. Create in-db tools workflow using indbc connection (that way you can also publish to alteryx server with no issues)
I also suggest - for assurance - to change name of 'net_service_name' attribute in tnsnames file to note it is 64bit.
Once you save these and re-start alteryx you should see new conection name.
https://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm#NETRF260
net_service_name64=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))