cx_Oracle doesn't connect when using SID instead of service name on connection string
I a similar scenario, I was able to connect to the database by using cx_Oracle.makedsn()
to create a dsn string with a given SID
(instead of the service name):
dsnStr = cx_Oracle.makedsn("oracle.sub.example.com", "1521", "ora1")
This returns something like
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.sub.example.com)(PORT=1521)))(CONNECT_DATA=(SID=ora1)))
which can then be used with cx_Oracle.connect()
to connect to the database:
con = cx_Oracle.connect(user="myuser", password="mypass", dsn=dsnStr)
print con.version
con.close()
For those looking for how to specify service_name instead of SID.
From changelog for SQLAlchemy 1.0.0b1 (released on March 13, 2015):
[oracle] [feature] Added support for cx_oracle connections to a specific service name, as opposed to a tns name, by passing
?service_name=<name>
to the URL. Pull request courtesy Sławomir Ehlert.
The change introduces new, Oracle dialect specific option service_name
which can be used to build connect string like this:
from sqlalchemy import create_engine
from sqlalchemy.engine import url
connect_url = url.URL(
'oracle+cx_oracle',
username='some_username',
password='some_password',
host='some_host',
port='some_port',
query=dict(service_name='some_oracle_service_name'))
engine = create_engine(connect_url)
If you are using sqlalchemy and ORACLE 12, the following seems to work.
from sqlalchemy import create_engine
con='oracle://user:password@hostname:1521/?service_name=DDDD'
engine = create_engine(con)
Note, you have to use the service name and not the SID. I don't know why, but the simple connection string that uses SID does not work.