I have a connection string that looks like this
con_str = "email@example.com:1521/ora1"
ora1 is the SID of my database. Using this information in SQL Developer works fine, meaning that I can connect and query without problems.
However, if I attempt to connect to Oracle using this string, it fails.
cx_Oracle.connect(con_str) DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This connection string format works if the
ora1 is a service name, though.
I have seen other questions that seem to have the reverse of my problem (it works with SID, but not Service name)
What is the proper way to connect to Oracle, using
cx_Oracle, using an
SID and not a service name? How do I do this without the need to adjust the
TNSNAMES.ORA file? My application is distributed to many users internally and making changes to the
TNSNAMES file is less than ideal when dealing with users without administrator privileges on their Windows machines. Additionally, when I use service name, I don't need to touch this file at all and would like it keep it that way.
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
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)