Issue Connecting to Oracle

[ciollid2]

Hello,

I am having an issue with the connection string required by connectorx.readsql().
I am using the template suggested by the documentation: 'oracle://username:password@server:port/database'
However, I get the error: ORA-12154: TNS:could not resolve the connect identifier specified

I can connect to this same Oracle DB through SQLAlchemy by using the following connection string:
oracle+cx_oracle://{user}:{password}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host})(PORT={port}))(CONNECT_DATA=(SERVICE_NAME={service_name}))

The portion of the string after the '@' is generated by the cx_Oracle.make_dsn function. My username & password contain only alphanumeric characters.

Any help would be greatly appreciated. This package seems to have a ton of potential and I'm excited to try it out.

[wangxiaoying]

Hi @ciollid2 , is your servername has special characters, if so maybe urlencode your servername? Also are you using the newest version?

[ciollid2]

Thanks for the quick reply
I am on connectorx 0.3.1 and Python 3.10.5.
There are no special characters in any information I am using in the string. Just to be sure I did try URL encoding everything anyway and it did not work.

[wangxiaoying]

That's weird, the error message does implies that the server cannot be reached. Can you confirm that the database in your connection string is the service_name? Like this comment.

[ciollid2]

Hello,
Sorry for the late reply. I have gone through each of the Oracle issues posted here and none of the solutions there worked.

My SQLAlchemy connection to our Oracle DB does not work formatted without the make_dsn information. Is it possible the (PROTOCOL=TCP) is required and not being passed? I am not too knowledgeable when it comes to networking.

Thanks

[wangxiaoying]

Is it possible the (PROTOCOL=TCP) is required and not being passed?

I think (PROTOCOL=TCP) is is not the issue since others mentioned the description string also contain the same (PROTOCOL=TCP).

Can you try to use command line like sqlplus to test (in the same env with your code). Basically try to see whether this works:

sqlplus ${ORACLE_USER}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_DB}

[ciollid2]

The sqlplus template you provided did not work for me, however I could connect with the following format:
sqlplus user/password@SID

Thanks

[wangxiaoying]

The sqlplus template you provided did not work for me

I think the reason for this should be the same with using connectorx, which to me seems like your instance cannot be reached using host:port/db fashion.

[ciollid2]

Alright, I will see if I can figure out why I cannot use that connection string.
Thanks for all your help.