Main menu

Post navigation

SQLDeveloper doesn’t like Mondays – refusing to play with TNS defined connection

OK, so I didn’t find this until today ( Wednesday). Look it’s poetic license alright ? Give me a break here !
Anyway, it seems that SQLDeveloper has decided to stop playing nicely and when trying to connect to XE on my TNS defined connection.
In the edit screen for the connection, when I tried to connect, it came up with :-

Status – Failure – Test Failed : no ocijdbc11 in java.library.path

Furthermore, when I tried to setup a new TNS connection, it refused even to give me a list of Network Aliases.
In answer to your next question – Yes, I did try turning it off and on again.
All of this seems a little strange as I’ve not updated my system since the last time I used SQLDeveloper and it’s been quite happy up until now.
Obviously, further investigation is required. In the meantime, I’ve managed to connect by redefining my connection as you can see from this screenshot…

A basic SQLDeveloper Connection

As you can see, I’ve defined the Connection Type as Basic.
This immediately brings up a different set of fields at the bottom of the New / Select Database Connection window.
The Network Alias and Connection Identifier fields are replaced by Hostname, Port, SID and Service Name.
The requisite fields were already populated with the appropriate values for me, but just in case your experience is a bit different, you need to enter the following values into the fields :-

Hostname – the name of the server on which your database is running ( localhost if it’s running on the same machine)

Port – the port the TNSListener is running on ( 1521 is the default)

SID – the SID of the instance you want to connect to ( XE is the default SID for an XE database)

You don’t need to specify a Service Name unless you’ve done something creative with the TNS configuration ( which I haven’t ).
Oh well, next item on the To-Do list is to find out why SQLDeveloper has suddenly decided to sulk.

Update : I’ve now managed to work out what the issue is. I’ve posted the solution here.

First off thanks for the advice and apologies for taking so long to get back to you.
I have been busy…er…eating and drinking too much !
Having looked at the article you refer to, and played around a bit, I think that this may be to do with the fact that SQLDeveloper relies on environment variables being set to find the tnsnames.ora. When I run it from the Ubuntu desktop, I’m not starting a shell, so the $ORACLE_HOME never gets set. This means that it doesn’t know where to look for the tnsnames.ora.
I’ve tested this by copying the tnsnames.ora to /etc and this seems to work. I’ll post another entry on the blog when I’m sure that this theory is correct.