CODEWITHSUNDEEP
pythonoracle-databasesqlpluspython-oracledb
Bhavin
Bhavin

Reputation: 45

Python : Not able to connect to oracle db ORA-12537: TNS:connection closed

I am trying to connect to oracle database in mac system.

My team generally works on Java so for development. we generally do this to connect to db in java. We connect to ssh tunnel which allows us to connect to remote host. And we have oracle db wallet which we mentioned properties file.

So with help of this ssh tunnel and wallet I am trying to connect in python as well. After looking over internet over various resource, I created below code.

import oracledb
import os


# Values
local_port = 1522
service_name = "mydb_high"
username = "read_only"
password = "pwd"
wallet_path = "/path/to/wallet/folder"
lib_dir = '/path/to/oracle_client/folder'

# Set the TNS_ADMIN environment variable to your wallet directory
os.environ["TNS_ADMIN"] = wallet_path

# Initialise Oracle Client
try:
    oracledb.init_oracle_client(lib_dir=lib_dir)
    
except Exception as e:
    print("Error initializing Oracle Client:", e)

dsn = "127.0.0.1:1522/" + service_name

try:
    # Connect to the database
    
    connection = oracledb.connect(
        user=username,
        password=password,
        dsn=dsn
    )
    
    print("Connected to Oracle Database")

    # Create a cursor object to execute queries
    cursor = connection.cursor()

    # test the connection
    cursor.execute("SELECT sysdate FROM dual")
    result = cursor.fetchone()
    print("Current Date/Time from Oracle DB:", result[0])

except oracledb.DatabaseError as e:
    error, = e.args
    print(f"Database connection failed: {error.message}")

finally:
    if 'connection' in locals() and connection:
        connection.close()
        print("Connection closed.")

When I am running this above code, I am getting this ORA-12537: TNS:connection closed. I tried looking up the error online but all solution says it's related to connection string and incorrect wallet file. But same wallet file is working perfectly file for sqldeveloper and java applications.

one of the solution asked me to test oracle_client connection using sqlplus from terminal. sqlplus 127.0.0.1:1522/mydb_high

Which When I am trying I am getting this error.

ORA-12162: TNS:net service name is incorrectly specified

My sqlnet.ora file which is present in wallet directory. looks like this.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=no

I even updated it with this,

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/path/to/wallet/folder")))
SSL_SERVER_DN_MATCH=yes

This doesn't help as well.

Can you please help me with this ?

Upvotes: 0

Views: 131

Answers (1)

Bhavin
Bhavin

Reputation: 45

By changing the dsn from localhost/service_name to actual dsn string present in tnsnames.ora file, it worked.

dsn = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=localhost))(connect_data=(service_name=service_name))(security=(ssl_server_dn_match=no)))"

Upvotes: 0

Related Questions