CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
YQ.Wang
YQ.Wang

Reputation: 1177

Unable to connect Python to Oracle

I want to connect Python to local Oracle with cx_Oracle. I know that in order to make the connection, I should know the local IP, port and SID. So I get these information through the following steps:

local IP:

import socket
#get local IP:
localhost = socket.gethostbyname(socket.gethostname())

port: This is from my listener.ora so I use port 1521.

# listener.ora Network Configuration File: C:\app\413022472\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\413022472\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\413022472\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-2RE9AJU.local)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (orcl)

sid:This bothers me a lot. I tried a lot but still can't get the correct sid. This is from my tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\app\413022472\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-2RE9AJU.local)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

This is what I got when running select instance_name from v$instance; in sqlplus:

INSTANCE_NAME
--------------------------------
orcl

I tried the following:

import cx_Oracle
import socket
#get local IP:
localhost = socket.gethostbyname(socket.gethostname())
#generate dsn:
dsn = cx_Oracle.makedsn(localhost, '1521', service_name = 'orcl')
#make connection:
conn = cx_Oracle.connect("c##scott", "tiger", dsn)

Which gives me:DatabaseError:ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I also tried dsn = cx_Oracle.makedsn(localhost, '1521', sid = 'orcl') Which gives me:ORA-12505: TNS:listener does not currently know of SID given in connect descriptorD

I should tell that I changed the text in listener.ora and tnsnames.ora but I can't remember the details. Could anyone help me make the connection success?

Upvotes: 0

Views: 3473

Answers (3)

Vishakha Lall
Vishakha Lall

Reputation: 1314

From my experience this can be a really frustrating error to debug. You probably solved the error but l'll add my solution here for anyone who struggles with a similar error connecting to Oracle through cx_Oracle.

Important points to note here:

  1. Although this is a database error, it would not be solved by killing and restarting the database listened service, it is to do with our connection through cx_
  2. Make sure the Oracle client and Python have the same architectures, either both are 32 bit or both are 64 bit. Here's how you can check your Python architecture: import platform platform.architecture()
  3. Make sure the environment path variable has the complete path of the Oracle client till the bin folder.
  4. Make sure to set the TNS_ADMIN variable to where the TNS file exists.
  5. Connection: import cx_Oracle conn = cx_Oracle.connect('<username>','<password>','<service name variable in your TNS file>') This method uses the connection details provided in the TNS file and cx_Oracle accesses the host and port details from the file itself.

Upvotes: 0

Pitto
Pitto

Reputation: 8579

I would use a snippet from this Oracle tutorial (remember to change user/pass accordingly).

import cx_Oracle
con = cx_Oracle.connect('pythonhol/[email protected]/orcl')
print con.version
con.close()

enter image description here

Here's the link to the official cx_Oracle Documentation.

As a last thing, at this GitHub URL you can find several updated examples that could be of help in speeding up your work.

Upvotes: 1

user8406805
user8406805

Reputation:

In listeners, there are many things that effect in creating a connection. Below is the content of "listener.ora" file as per my system. Before that verify your hostname is valid or not by using "hostname" in command prompt. Before replacing the content of listener file takes a copy of your existing listener.ora file. Once you modified the listener.ora file then restarts the listener and Oracle service as well. 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-2RE9AJU.local)(PORT = 1521))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\413022472\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\413022472\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
   (SID_DESC = 
      (GLOBAL_DBNAME = orcl.com)
      (ORACLE_HOME =C:\app\413022472\product\12.2.0\dbhome_1)
      (SID_NAME = orcl)
    )

  )

DEFAULT_SERVICE_LISTENER = (orcl)

Upvotes: 1

Related Questions