0

I use Toad to connect to my Oracle database and that works fine however I am trying to use cx_Oracle to connect to the same database and my program gets stuck at the .connect() method.

I believe the issue is the host name but I cant be sure as no errors exist just does not get past the connect() call.

import cx_Oracle


CONN_INFO = {'host': '\\\\SERVERNAMEUSEDINTOAD\\',
             'port': 1111,
             'user': 'USER123',
             'psw': 'password',
             'service': 'dbname.somesite.com'}
print("test1")
CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)
print("test2")
con = cx_Oracle.connect(CONN_STR)
print("test3")
print(con.fetchmany([1]))

Console:

test1
test2

I make it to test2 but not test3. Am I using the host name correctly? The host is an internal server on the network.

TOAD application displays just SERVERNAMEUSEDINTOAD so I tried that as well:

'host': 'SERVERNAMEUSEDINTOAD'

But this resulted in the following error:

test1
test2
Traceback (most recent call last):
  File "C:/Users/name/PycharmProjects/WorkFlow/test.py", line 12, in <module>
    con = cx_Oracle.connect(CONN_STR)
cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • `print(CONN_STR)` instead of `test2` would be more interesting… the error given suggests that the resulting `CONN_STR` isn't what it needs (stating the obvious). The backslashes in the first CONN_INFO don't look fine to me… Is what you've took from Toad really the server host or the TNS name of `tnsnames.ora`? See if [this answer](https://stackoverflow.com/questions/245465/cx-oracle-connecting-to-oracle-db-remotely) can help you someway — from an answer there, your second string should be fine, provided the connection data are correct. – ShinTakezou Feb 01 '19 at 21:09
  • 1
    @ShinTakezou I have tried that as well and it never prints. Like its hung up. That said I have resolved the issue by using `makedsn()` first then connect with those settings. – Mike - SMT Feb 01 '19 at 21:11

2 Answers2

0

After doing some digging I found this post:

cx_Oracle doesn't connect when using SID instead of service name on connection string.

Thought it was about connecting with the SID there was a post that suggested to use makedsn() and then use this to connect.

Turns out that solved my issue as well with connecting via the service_name.

I am posting my solution here so anyone who finds this post will have a option to fix this problem.

import cx_Oracle

or_dns = cx_Oracle.makedsn('SERVERNAMEUSEDINTOAD', 1111, service_name='dbname.somesite.com')

con = cx_Oracle.connect(user="USER123", password="password", dsn=or_dns)
print(con)

Console:

<cx_Oracle.Connection to USER123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVERNAMEUSEDINTOAD)(PORT=1111))(CONNECT_DATA=(SERVICE_NAME=dbname.somesite.com)))>
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • add print(or_dns) after your call to makedsn. The output is a string that looks like a tnsnames.ora entry. I wonder if this is a cx_oracle bug. This page makes you think it should work as you originally did it. https://www.oracle.com/technetwork/articles/dsl/python-091105.html – Bobby Durrett Feb 01 '19 at 21:32
  • @BobbyDurrett I will have to check Monday as I am leaving the office. – Mike - SMT Feb 01 '19 at 21:41
  • No worries. It looks like replacing the host name with the ip address works. Have a good weekend. – Bobby Durrett Feb 01 '19 at 21:47
0

I had to do two things to get your example to work on my laptop.

I had to update my sqlnet.ora to have this line:

names.directory_path = (TNSNAMES,EZCONNECT)

instead of

names.directory_path = (TNSNAMES)

Then I had to use the ip address for my host instead of the hostname.

I do not think this is a bug in cx_oracle because sqlplus did not work either with the same syntax. But, it worked with the ip address and EZCONNECT in my sqlnet.ora.

Bobby

Bobby Durrett
  • 1,223
  • 12
  • 19