Oracle Connectivity Issues

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

good connection xiw.PNG

query in sql_dev.PNG

scpo alteryx config.PNG

scpo config sql dev.PNG

tnsnames file.PNG

Input

Error Message

Database Connection

Accepted answers

patrick_digan

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

patrick_digan

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

All comments

patrick_digan

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

michael_heitz

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

michael_heitz

thanks for your assist Patrick... once I used the IP address instead of the host name in the Alteryx config settings, it successfully connected!

marcin2x4

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)))

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703