ThatJeffSmith

On SQL Developer and TNSNAMES.ORA

Tnsnames.ora [DOCS] is a configuration file for SQL*Net that describes the network service names for the databases in your organization. Basically, it tells Oracle applications how to find your databases.

This post is just a quick overview on how to get SQL Developer to ‘see’ this file and define a connection.

There’s only a single prerequisite for having SQL Devleoper setup such that it can use TNSNAMES to connect:

  1. You have somewhere a tnsnames.ora file

You don’t need a client, instant or otherwise, on your machine. You just need the file.

Now, if you DO you have a client or HOME on your machine, SQL Developer will look for those and find the tnsnames file for you. IF we can’t find it at the usual places, you can simply tell us where it is via this preference:

On the Database - Advanced page

On the Database – Advanced page

Once you’ve done this, assuming you have a file (or 10) in that directory, we’ll read it, parse it, and list the entries in the connection dialog.

The File(s)

That’s right, files. Just like SQL*Plus, we’ll read any file that starts with ‘tnsnames’ – that includes files you’ve renamed to .bak or .old. Kris talks about that more here. I have just the one, which is all I need anyway.

There we go!

There we go!

Defining the Connection

Just set the connection type to TNS.

This is a lot easier to do than manually defining the connections - esp as they're likely to frequently change in 'the real world.'

This is a lot easier to do than manually defining the connections – esp as they’re likely to frequently change in ‘the real world.’

No Client or Home Required

That’s right. You don’t need an Oracle Client or $ORACLE_HOME to have SQL Developer see and read a TNS file.

Just so you know I’m not cheating…

SQL Dev doesn't know which client to use and won't use it even if it DID know...

SQL Dev doesn’t know which client to use and won’t use it even if it DID know…

I’m able to define a new connection AND connect with these preferences ON|OFF.