One of the most common TOAD questions or problem areas is with connecting to remote and/or local databases. The process and requirements are actually quite simple – yet this remains a high frequency question that seems to cause remarkable confusion. And not just for new Oracle or new TOAD users, as sometimes even old pros can forget and ask. They just often do a Homer Simpson “Doh” once they hear the answer

Now there are literally dozens of scenarios that you might encounter, I am not going to show them all. Instead I’ll show you a common yet easy one to understand and troubleshoot. Then you simply need to apply your new “detective skills” to solve the other scenarios you might run into. Just remember, TOAD has two million+ users – and if we had a real connection problem we’d have heard about it and fixed it. Almost every time you get an unable to connect to database error message – it’s a local PC or possibly even a database server listener configuration problem. I’m not simply trying to pass the buck here. I’m just telling you that it’s the rare exception and not the rule – connection errors are most likely on your end, so investigate before just saying TOAD’s broken.

The most common scenario we run into is that people say “I can connect with Oracle SQL*Plus on my PC and TOAD gives an Oracle error – so TOAD does not work for me or my very special conditions. So here’s a screen snapshot of SQL*Plus working on my Windows XP PC.

OK – so SQL*Plus works on my PC. Now look at this screen snapshot – same PC yet TOAD won’t connect.

So let’s see what I did to get this wonderful Oracle error ORA-12154 (just one of the three to four error messages you’re likely to see while your SQL*Net environment or configuration is not right). Look at the first three solid arrows – I gave the correct user ID and password, plus the correct Oracle SID or instance ID. So why did I get this database connection error? Look at the crossed, fatter errors near the bottom right hand corner – these are you clues.

I have instructed TOAD to use the Oracle 11g client on my PC, and the red “X” next to the “TNSNames Editor” button indicates that there is no TNSNames.ora file. OK – but then why did SQL*Plus work? Look again back at the first screen snapshot. It appears that I have run SQL*Plus from Oracle 10g somehow? If I then do a right hand mouse on “MY Computer” on my desktop and choose properties, the Windows “System Properties” window will pop-up. Now if I next choose the “Advanced” tab and then press the “Environment Variables” button, the window in the next screen snapshot comes up. I finally double click the “PATH” so I can review and possibly edit my PATH environment variable – because here’s where the problem lies. Look at the screen a moment before reading on.

OK – my environment has the Oracle 10g home first in my PATH order. So when I run SQL*Plus it starts the binary from Oracle 10g and by default looks for the TNSNames.ora file under the same home – so it works because there is a TNSNames.ora file there. We know that for sure because as this next and final screen snapshot from TOAD shows, when I use the Oracle 10G home the red “X” turns to a green -> and the “TNSNames Editor” button indicates that there is a valid TNSNames.ora file. This attempt to connect using TOAD works – and works just fine.

So the key lesson or conclusion to take away from this blog is as follows: TOAD can connect to Oracle databases and it works 99.99% of the time. If you have a problem and get some Oracle connection error message, odds are that you have one of the following problems: a bad SQL*net install, are pointing to the wrong Oracle Home, have a Windows PATH environment ordering problem, or some other user setup and configuration problem related to your Windows and/or Oracle home.

So please apply this type of investigative process and thinking anytime that you cannot connect to your database with TOAD. TOAD could never have achieved such huge adoption and success if it could not connect to peoples’ databases. Thus the problem is most likely some minor little thing specific to your PC. Because in the last ten years I can count on one hand the real database connection problems that people have run into. It’s that rare.

4 comment(s) so far...