Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Oracle Connectivity Issues

michael_heitz
8 - Asteroid

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

 

6 REPLIES 6
patrick_digan
17 - Castor
17 - Castor

@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
8 - Asteroid

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
8 - Asteroid

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

patrick_digan
17 - Castor
17 - Castor

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

 

cheerspankaj
7 - Meteor

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:

  1. Install the oracle instant client and ODBC package from oracle site( for oracle 12.1..)
  2. Added environment variable for TNS_ADMIN and added path for instant client
  3. Ran ODBC installation exe
  4. Created ODBC for Oracle data source

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

 

mszpot89
9 - Comet

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

 

image.png

2. Add above path to 'Path' variable also:

image.png

 

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

 

Labels