cx_Oracle doesn't connect when using SID instead of service name on connection string
Solution 1
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()
Solution 2
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)
Solution 3
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.
Andy
Elected Moderator ♦ on Stack Overflow Moderator ♦ Pro Tempore on Community Building and Hardware Recommendations
Updated on April 13, 2020Comments
-
Andy about 4 years
I have a connection string that looks like this
con_str = "myuser/[email protected]:1521/ora1"
Where
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)
- Using Oracle Service Names with SQLAlachemy
- Oracle SID and Service name; connection problems
- cx_Oracle & Connecting to Oracle DB Remotely
What is the proper way to connect to Oracle, using
cx_Oracle
, using anSID
and not a service name? How do I do this without the need to adjust theTNSNAMES.ORA
file? My application is distributed to many users internally and making changes to theTNSNAMES
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. -
dmvianna over 7 yearsThe documentation is very clear. Just do
cx_Oracle.makedsn("oracle.sub.example.com", "1521", service_name="ora1")
, explicitly using the keywordservice_name
to differentiate it from the third argument (which issid
). -
S4nd33p almost 7 yearsVery useful, landed here after 2 days of search and it worked in less than 30 seconds. Terrific. Thanks a lot @Andreas Fester.
-
Andreas Fester almost 7 years@S4nd33p Thanks, glad to hear that it helped :-)
-
xappppp about 5 yearsAfter one month of search, and playing with different kinds of directory and environment variable, this is the answer fianlly get me connectted to the server and start to pull data. Very nice
-
Superdooperhero about 3 yearsThis is question about
cx_Oracle
notsqlalchemy