Forum Stats

  • 3,770,265 Users
  • 2,253,087 Discussions
  • 7,875,378 Comments

Discussions

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

user9336373
user9336373 Member Posts: 3
edited Sep 5, 2017 11:48PM in Python
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.

user9336373

Best Answer

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Sep 5, 2017 11:39AM Accepted Answer

    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

Answers

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Sep 5, 2017 11:39AM Accepted Answer

    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
  • user9336373
    user9336373 Member Posts: 3
    edited Sep 5, 2017 11:46PM

    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
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Sep 5, 2017 11:48PM

    You're welcome. Glad to be of help!

This discussion has been closed.