Error while connecting with Oracle 12c using cx_oracle

Question

I am trying to connect with an Oracle 12c database using cx_oracle. My code is listed below:

import cx_Oracle
from cx_Oracle import DatabaseError
import pandas as pd
import credaws
import os

os.system('export ORACLE_HOME=/opt/app/oracle/product/client_12_2')
os.system('export PATH=$ORACLE_HOME/bin:$PATH')
os.system('export LD_LIBRARY_PATH=$ORACLE_HOME/lib')

try:
    # cx_Oracle.init_oracle_client(lib_dir=libdir)
    dsn_tns=cx_Oracle.makedsn(credaws.host_name,credaws.port_number,service_name=credaws.service_name)
    conn = cx_Oracle.connect(user=credaws.user,password=credaws.password,dsn=dsn_tns)
    if conn:
        cursor = conn.cursor()
        print('Connection Successful')
except DatabaseError as e:
    err, = e.args
    print("Oracle-Error-Code:", err.code)
    print("Oracle-Error-Message:", err.message)
    
finally:
    cursor.close()
    conn.close()

I'm still getting this error:

Oracle 12c is installed in /opt/app/oracle/product/client_12_2 location. What am I doing wrong?

Edit 1: I setting ORACLE_HOME, PATH and LD_LIBRARY_PATH environment variables before calling cx_oracle connect method. However, still getting the same error.

Edit 2: When running this script as oracle user, I'm getting below error:

I'm facing the same issue with Oracle 19c. I would appreciate if someone can answer this. — Yash Sharma, May 17 '21 at 12:53
Check out this answer? https://stackoverflow.com/questions/23970158/ora-12170-tnsconnect-timeout-occurred — Anthony Tuininga, May 17 '21 at 19:48
I tried setting the ORACLE_HOME, PATH and LD_LIBRARY_PATH variables. However, still getting the same error. Updated the code in the question. — Abhinav Dhiman, May 18 '21 at 06:06

Samuel Nyong'a · Answer 1 · 2021-10-26T19:36:34.827

This question just assisted me big time. I have been struggling to connect to Oracle Database for a while. I just passed in the connection string directly to connect to the database and it worked. It is risky but the script is for my personal use. I new to python. Here is my code.

import os  
import cx_Oracle  

os.system('set ORACLE_HOME=C:\\oraclexe\\app\\oracle\\product\\10.2.0\\server')  
os.system('set PATH=$ORACLE_HOME/bin:$PATH')  
os.system('set LD_LIBRARY_PATH=$ORACLE_HOME/lib')  

#Test to see if the cx_Oracle is recognized  
print(cx_Oracle.version) # this returns 8.2.1  

cx_Oracle.clientversion()  
# I just directly passed in the connection string
con = cx_Oracle.connect('USERNAME/PWD@SERVER:PORT/DATABASENAME')  
cur = con.cursor()  
try:  
    # test the connection by getting the db version  
    print("Database version:", con.version)  

    cur.execute("select * from products order by 1")  
    res = cur.fetchall()  
    print('Number of products is ',len(res))  
    for row in res:  
        print(row)  
except cx_Oracle.DatabaseError as e:  
    err, = e.args  
    print("Oracle-Error-Code:", err.code)  
    print("Oracle-Error-Message:", err.message)  
finally:  
    cur.close()  
    con.close()

Your `os.system()` calls use a mix of Windows and Linux syntax. Also setting LD_LIBRARY_HOME inside a running process never works - it must be set before the process starts. It would be better to follow the cx_Oracle doc and examples on initialization: https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html — Christopher Jones, Oct 28 '21 at 01:18

Error while connecting with Oracle 12c using cx_oracle

1 Answers1