Oracle Net unaccountability

I have a complaint. After seven years of Oracle experience, ORA-12154: TNS:could not resolve the connect identifier specified ought to be in my past.

I installed a standard Oracle 10.2 client on a fresh, new machine, only to find that the sqlplus.exe in {ORACLE_HOME}/bin was not looking in {ORACLE_HOME}/network/admin for its TNSNAMES.ORA. I don't know where it was looking, or why. I searched for any of those annoying stray TNSNAMES.ORA files, and there weren't any, but that doesn't mean that Oracle Net wasn't looking for TNSNAMES in all the wrong places. I eventually gave up and set the TNS_ADMIN environment variable, but that was an unsatisfying brute-force solution; I want to know why SQLPLUS wasn't looking in the standard place in its own home for TNSNAMES.ORA, but apparently I'll never know.

The problem is that Oracle Net gives you no feedback about what went wrong when something goes wrong. Did it find a TNSNAMES.ORA, but hit a syntax error in it? Did it find a TNSNAMES.ORA, but not the one you expected it to? Did it not find a TNSNAMES.ORA at all? Well, you'll just have to guess. Yes, you can trace Oracle Net; you need to insert directives like TRACE_LEVEL_CLIENT=user and TRACE_DIRECTORY_CLIENT into SQLNET.ORA. Ah, but which SQLNET.ORA? Well, that's the problem - if Oracle Net isn't looking where you expect it to for TNSNAMES.ORA, it won't see your SQLNET.ORA either.

It was a big improvement when TNSPING was upgraded to report on which SQLNET.ORA it was using. (And, indeed, in my case, TNSPING reported that it was using {ORACLE_HOME}/network/admin/sqlnet.ora, as you would expect, which is probably why TNSPING could resolve my service names just fine.) We really need a similar improvement in Oracle Net in general - some troubleshooting information that's transmitted in the error message every time TNS resolution fails.

Have I simply missed the memo on some good way to troubleshoot these problems? If you know of one, please let me know.

While researching it, I did find a pretty nice resource - ora-code.com. Their ORA-12154 page is a more concise and relevant checklist than anything I know of on MetaLink. Unfortunately, they lack a search box, so the best way to find their pages is simply to Google for "ora-code 12154".

There. Now if Oracle magazine ever asks me "what one improvement I'd like to see in Oracle", I have my answer ready. That and PL/Python, of course.

resetPwd.py

I just posted a little script at http://sqlwrappy.sourceforge.net/resetPwd.py for password changing; a full description is at http://sqlwrappy.sourceforge.net/resetPwd.html. You may like to use it / borrow from it if

• You need to provide for nontechnical users who must field "Can you reset my password?" requests (the designed purpose)
• You want a relatively robust command-line way to collect Oracle account login information
• You want to crib code for a command-line pick-from-this-list loop

I packaged it into the 0.1.2 release of sqlWrap.py, too.

The destructive power of stereotypes

I hope that this study receives all the attention it deserves.

"Women exposed to bogus scientific theories linking their gender to poor math skills did worse on arithmetic tests than others..."

This is why people who care get so upset about "harmless personal opinions" about women being inherently worse at acience/math. They're not harmless. They push women away from technical excellence. Yes, that bothers me, and no passive-aggressive whining about "political correctness" will make me accept it in demure silence.

It's especially remarkable that the study showed an easily measurable effect of just one claim of male superiority. Now imagine the cumulative effect of hearing such claims, again and again, over an entire lifetime... it's no wonder that the women who do end up in technology are the exceptionally flinty ones.

meaning of LAMP

Among the great things I learned at Ohio LinuxFest came from Jeff Waugh's talk. I bet you've seen that LAMP acronym around, and wondered what it stood for.

Linux
Apache
Most scripting languages begin with "P"
PostgreSQL

Now, that makes a lot more sense.

Oracle BoF at LinuxFest

Thank God, the babysitter, and a wonderful spouse, I did get to go to Ohio LinuxFest after all. Hooray!

With one talk each on MySQL and PostgreSQL, it was a good day for database enthusiasts. Maybe that's why I saw quite a few Oracle users there.

The organizers also invited attendees to put together impromptu Birds-of-a-Feather (BoF) sessions. I was tempted to throw together one for Oracle, but I felt like my impromptu idea needed more preparation... I wanted to have a list of topics to seed discussion if it drags.

So, what kind of topics would you suggest for an Oracle-Linux BoF? Here are some that occurred to me.

• Experiences with Oracle on various Linux distributions
• Oracle's ancient Apache 1.3 HTTPServer; is it ever going to get to 2.0? Can you get mod_plsql working on Apache 2.0?
• Open-source SQL*Plus alternatives (actually, someday I hope to talk at OOUG on this)
• Experiences with RAC on Linux, Oracle Cluster File System, etc.?

Please, add topics to this list! And, if you're going to be at next year's Ohio LinuxFest, come be part of an OraBoF!

Oracle-free Oracle access

I got an interesting question from Guido d'Amico... he wants to use Python scripts to access Oracle databases from machines with no Oracle software installed. Between us, we came up with these options.

• cx_Oracle and DCOracle2: These "classic" DB-API2 modules both rely on the OCI (Oracle Call Interface), a piece of software distributed by Oracle. (I believe all comparable means for accessing Oracle from other languages rely on the OCI, too.) There's just no way around that - you need some sort of Oracle client installed on the machine you're using them from.
  You don't have to bulk up your machine with a full-blown standard Oracle client, though.
  • Oracle Instant Client is lightweight (85 MB on my Windows box), free, and redistributable. For better and worse, it comes as a simple zipped set of files - if you want any environment variables set (ORACLE_HOME, PATH), you need to do that yourself.
  • OracleXEClient is likewise lightweight (72 MB) and free, and very easy to install.
  Neither of these options comes with a /network/admin/ folder, which might be a little confusing - unless you want to make your connections with Easy Connect, you'll need to set up $ORACLE_HOME/network/admin/tnsnames.ora by yourself.
• You can use ODBC. mxODBC has been around for a while, but is not free for commercial use. pyODBC is free, and I hadn't actually heard of it until I researched this question - maybe I'll review it (or at least find a review) sometime soon.
• You can use JDBC from Jython. Andy Todd and Przemek Piotrowski have blog posts detailing this.
• You can go to IronPython and... um... OK, I've never yet done database access from IronPython, but I assume that using ODT.NET from IronPython is easy enough.
  
  EDIT: Przemek Piotrowski has not just made it work, he's posted a tutorial on ODP under IronPython. Thanks, Przemek!