ORA: 12154 error while connecting to Oracle 11g database using python 3.6

user9336373

import cx_Oracleprint('connection start')db_connection = cx_Oracle.connect("jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Host1>)(PORT = <port_number>)) (ADDRESS = (PROTOCOL = TCP)(HOST = <Host2>)(PORT = <port_number>)) (FAILOVER=true)(LOAD_BALANCE=true) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <service_name>) (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))", "<username>", "<password>")print(db_connection)print('connection successful')

I'm trying to connect to Oracle 11g database using the above python 3.6 code. However, I'm encountering the following error.

DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

I'm able to successfully connect to same oracle 11g database by using Oracle-SQL-Developer version 17 (with the same connection string).

So, I'm wondering if there is any issue with my code or does cx_Oracle needs supporting drivers etc. Any help would be much appreciated.

Anthony Tuininga-Oracle

cx_Oracle can't use JDBC thin to connect. You need to use an Oracle Client (like Instant Client which you can download from here: Oracle Instant Client Downloads). Then, you can use EZ connect as in the following:

db_connection = cx_Oracle.connect("user/[email protected]:port/service")

You replace the bits in the string with the values for your configuration. A default configuration might look like this:

db_connection = cx_Oracle.connect("cx_Oracle/[email protected]/orclpdb")

Essentially, any connect string that works with SQL*Plus should also work with cx_Oracle. You just can't use JDBC thin!

Hope that helps.

Anthony

Anthony Tuininga-Oracle

cx_Oracle can't use JDBC thin to connect. You need to use an Oracle Client (like Instant Client which you can download from here: Oracle Instant Client Downloads). Then, you can use EZ connect as in the following:

db_connection = cx_Oracle.connect("user/[email protected]:port/service")

You replace the bits in the string with the values for your configuration. A default configuration might look like this:

db_connection = cx_Oracle.connect("cx_Oracle/[email protected]/orclpdb")

Essentially, any connect string that works with SQL*Plus should also work with cx_Oracle. You just can't use JDBC thin!

Hope that helps.

Anthony

user9336373

Anthony, Thank you for your suggestion and I'm able to successfully establish a database connection now. I stripped down my complex connection string into "user/[email protected]:port/service" format and that solved the problem. Because my initial connection string has two hosts, i prepared two connections string one for each host in the suggested format and found out that one of the host connection is no longer valid and that is throwing me an error "ORA-12154".  I appreciate your quick response with a working solution.

Anthony Tuininga-Oracle

You're welcome. Glad to be of help!