I'm working on a .NET application that is supposed to connect to any OracleDB but I'm a bit stumped, trying to get it to work on different versions.
I originally started with Oracle 11g (Express), trying to connect was actually rather easy, I simply used the following Connection String and it worked:
Data Source=hostname:port;User Id=myUsername;Password=myPassword;
I then tried to connect to a 12c and it told me that I didn't specify the service name. This confused the shit out of me because I never had to specify the Service Name in 11 but I looked online and found another Connection String in this format:
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))
(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername; pwd=myPassword;
It still didn't work. A colleague gave me the tip to use the "Global Database Name" instead of the SID (ORCL.domainname.local instead of ORCL) and it worked after we added ORCL.domainname.local to our Domain Controller's DNS Server's forward lookup zones.
I then went back to 11g did the same things there but turns out using the new Connection String there doesn't work.
While we could probably implement some kind of "trying until it works"-feature in our connection string builder (user specifies the data) this seems like a terrible solution and I don't think it's a good idea. Also at this point we really want to know WHY so we can tell if it's a configuration issue or an absolute version issue.
Anyone care to give me some insight?
Best Answer
I have an answer for this right here:
What database name should I use to connect Tomcat with my database
Essentially there's 2 ways you can connect to the DB - with the SID or a Service Name. In Oracle 12c and up, the option for connecting via SID is essentially gone away.
A SID is not a good way to identify a database, all it really does is identifies a particular Oracle instance running in a particular Oracle Home.
You should be using service names to connect in 11g+.