The Secret Life of Connection Strings In Oracle! (Oracle DBA's Hate Me.)

I don't know if you've ever had to work with Oracle, but if you have, and if you're primarily a .net developer like me, I wonder if your experience is anything like my own.

The very first thing you do with Oracle, as a developer, is to connect to it. And you soon discover that the way connections are specified in Oracle is batshit insane.

With a SQL Server connection string, there are a couple of quirks*, sure. But essentially, the connection string holds all of the details that are used to connect. Every detail you could possibly want goes into the connection string.

(* Regarding connection strings. I've always wanted to register the site "www.FuckingConnectionStrings.com" which simply redirects to "www.ConnectionStrings.com" but is much much easier to remember.)

In Oracle, a connection string is just the first mirror in a long hallway full of mirrors. A hallway that leads into a maze of mirrors, that takes you to a forest filled with monsters and more mirrors.

We had an issue recently where a connection string that worked perfectly well on 7 different machines did not work on an 8th machine. When we looked into how the connection worked on those first 7 machines, we found it was different on every single one. When we took this knowledge to the 8th machine, we began to feel a terrible sense of foreboding. The further into the issue we looked, the more terrified we became. Eventually the authorities found our skeletal remains at the bottom of a steep ravine clinging to a well thumbed printout of Oracle documentation.

Here is how you usually connect to Oracle:

You follow some simple instructions, and you get some generic error messages. You're not panicking, you're just a little out of your depth. Eventually some oracle DBA scoffs into the room (that's the way they walk, they scoff as they walk) pushes you off your keyboard and does some magic incantations in front of your computer. You keep looking over their shoulder, trying to learn about this strange magic. You see strange registry keys, shocking environment variables, keywords like "tnsnames.ora" and some other disturbing flashes of technical wizard-pokery. Was that LISP!? For a moment you get one sickening glimpse into the infinite pit of sorrow.

After that it just works, and you soon forget how tricky it was, you confine it to a dark corner of your psyche. The rest of your experience with Oracle is never as daunting as that first connection. But by then the Oracle DBA has cemented their role as an irreplaceable technical God.

Well I'm here to blow the lid open on their whole racket.

I'm here to teach you all the things they don't want you to know. I'm going to expose their little shitshow and all of its pathetic quirks.

Get ready.

In an Oracle connection string, the Data Source attribute is used, not for connecting to remote machines, but for searching your local machine. I'll give you an example. If the connection string looked like this:

User Id=scott;Password=tiger;Data Source=bbSales12

What do you think happens with that Data Source value? (bbSales12)

Here's what happens....

The value of Data Source is an Alias. And Oracle wants to Resolve that alias.

According to the documentation, Oracle first looks in "the connection pool" to see if it has already "Resolved" that alias.

Given that, at this point, we haven't started talking to any remote machine, we must be referring to some kind of local connection pool. Probably some kind of in-memory dictionary. Documentation is sketchy of what that is. But let's press on. Ignore that bit.

Assuming we haven't resolved this alias before, then we need to look at tnsnames.ora.

What is tnsnames.ora I hear you ask?

It is the third mirror. It is magic. It is a file that contains enough configuration detail that your machine should be able to connect to the remote machine.

It is likely to be this file that the administrator was futzing with. (Or some other things that will be explained shortly)

First up: how does Oracle find the tnsnames.ora file?

To find this magical file, Oracle relies on an environment variable, TNS_ADMIN. That is the second mirror.

If you don't have this environment variable set, then Oracle will not be able to find the tnsnames.ora file (or the sqlnet.ora file, which will be described later)

With powershell you can look at all your "environment variables" like this:

Get-ChildItem Env:

Get-ChildItem has these two aliases that you might find more comfortable: dir, ls,

So you can type

dir env:

Or

ls env:

And to see value of the TNS_ADMIN environment variable, type $ENV:TNS_ADMIN

Now, assuming you have that variable set, it will show you a path. Follow that path, and look for a file named tnsnames.ora

(Some people will tell you that tnsnames.ora is located in ORACLE HOME\NETWORK\ADMIN. This is confusing advice that you can ignore. More on that later)

Was tnsnames.ora there? Good. Now look inside. It is a text file. So you open it with sublime or vs code, or whatever the cool kids are using at this time of day.

You should find the alias defined via a piece of configuration language like this:

bbSales12=
 (DESCRIPTION= 
  (ADDRESS= (PROTOCOL=tcp)(HOST=bb12Sales-server3)(PORT=1521))
  (CONNECT_DATA= 
     (SERVICE_NAME=sales.us.acme.com)))

What is that language? It looks a bit like lisp, or a bit like JSON. It sounds a lot like Greenspun:

Any sufficiently complicated program contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of Common Lisp.
—Greenspun's Tenth Rule

I haven't been able to find any official name for this syntax, other than "the syntax of the .ORA files". It's used by Listener.ora, tnsnames.ora, sqlnet.ora, protocol.ora and tnsnav.ora

(By the way, whitespace is insignificant, but recommended.)

Now -- what if you don't have a tnsnames.ora file? You could of course go ahead and create one, then try and put the right protocol, host, port and servicename into it.

But there's another intriguing possibility.

Way back at the start of the process, inside your connection string, you can embed one of these weird lisp-like strings directly.

Instead of

User Id=scott;Password=tiger;Data Source=bbSales12

You can have:

User Id=scott;Password=tiger;Data Source=
    (DESCRIPTION= 
        (ADDRESS= (PROTOCOL=tcp)(HOST=bb12Sales-server3)(PORT=1521))
        (CONNECT_DATA= 
        (SERVICE_NAME=sales.us.acme.com)))

How's that for a snappy connection string that rolls off the tongue. In official parlance what we've done is add a "connect descriptor" to our connection string.

Now you must be wondering.... what other syntaxes can this Oracle connection string withstand?

The answer is "pretty much all of them."

As an attempt at irony the wise folk of Oracle invented something called "the Easy Connect Naming Method".

This lets you specify a Data Source like this:

//host:[port]/[service_name]

For example:

"user id=scott;password=tiger;data source=//bb12Sales-server3:1521/sales.us.acme.com"

Now that looks much simpler doesn't it? Why am I stating that this is ironic? Because of this little footnote from the documentation:

Prior to using the easy connect naming method, make sure that EZCONNECT is specified by the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.

This introduces the "sqlnet.ora" file which is also searched for in the TNS_ADMIN folder.

Inside that file, if you specify

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

...then you'll be able to use "the Easy Connect Naming Method".

If you're brave you can also add LDAP and ONAMES into the mix. But that's outside the scope of this rant.

This EZCONNECT syntax really is a bit easier than the alternatives, so I don't think their attempt at ironic naming was a complete success ;).

Oracle Home.

Now I mentioned that some people will tell you that tnsnames.ora is located in ORACLE HOME\NETWORK\ADMIN

I'll cover what that means, but first I have to tell you, No! tnsnames.ora is not necessarily located in ORACLE HOME\NETWORK\ADMIN. It can be located anywhere! That's what the TNS_ADMIN environment variable is for: for allowing it to be located anywhere, irrespective of where "ORACLE HOME" is. (Your friendly system admins may locate tnsnames.ora on a network drive where it is easier to administer en-masse.)

Now what is this "Oracle Home" business, hmm?

That is the third mirror down the first hallway on the right. It is a dead end. But I will tell you about it anyway.

When (or rather "if, against all common sense") you installed Oracle, it will have been installed into a location that it refers to as the "Oracle Home".

It's possible that there is an environment variable called "ORACLE_HOME". But failing that -- the value of "Oracle Home" may be stored in the registry.

Where in the registry, exactly? Any one of these places my poor dear friend.

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

But as long as TNS_ADMIN is set correctly, you don't need to worry about those registry entries, or the absence of an ORACLE_HOME environment variable.

Okay, that's all I wanted to say about Oracle connection strings. In order to cleanse your mind of this horrible mess, I suggest you learn about how .net locates an assembly at runtime or look into error handling with asp.net MVC.

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.