Even Oracle supporters have to concede that it is just horribly hard to use, especially in its command line interfaces. I am not complaining (at least not this time) about the fact that you basically need to be a DBA in order to get anything done. It is a defensible position to require trained professionals to install such a complex system. I am talking about things like sensible default values, helpful diagnostic output, auto-completion, command history, adherence to generally acknowledged conventions and such.

I spent an hour today supporting one of our developers in fixing his Oracle connection problems, which turned out to be something extremely silly. Without the help of Google this could have potentially taken much longer.

There are a number of ways to connect to an Oracle DB using Oracle's own client software, the most popular one involves a text file tnsnames.ora that defines where the databases are.

MY_DATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = out.there)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Even though the settings looked okay, we could not connect to the database.

$ sqlplus me@MY_DATABASE
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

This message seemed to indicate that tnsnames.ora was somehow broken, or not being read. But on the other hand, Oracle's network ping tool (which reads the same file) had no problem.

$ tnsping MY_DATABASE

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = out.there)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

So maybe the file was okay? It was not, of course. But only because Oracle turned out to be extremely picky. tnsnames.ora had been copied from a Windows machine, and the Linux Oracle client did not like the Windows line breaks. Even if we accept that such behaviour is acceptable, having some of the tools handle the situation differently than others is very confusing.

If Oracle made it easier to access their bug database, I would search for a solution, or post a nice report there, but as it is, I'll just post it here. Hopefully, this does not violate the code of conduct for Certified Professionals.

SQL> select * from dual where (dummy,dummy) in (((select dummy,dummy from dual)));

select * from dual where (dummy,dummy) in (((select dummy,dummy from dual)))
              *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkqtnlop2Cbk : 1], [], [], [], [],
[], [], []

Note that this actually crashes Oracle. Also note that it works as expected when there is only one column in the inline view, or if there is one pair less of superfluous brackets around it.

Update: While it crashes my 10g R2 XE (on Debian and on Windows), R1 seems safe...

Oracle Database 10g Express Edition. Free to develop, deploy, and distribute. Now in Beta for Linux and Windows. Limited to 4GB of user data, 1 CPU and 1 GB of memory. Includes HTML DB and Oracle Text. Uses native installers instead of Oracle Universal Installer. Self-managed. Ready to run within two minutes after download. Did I mention free (as in beer, of course)?

After several months delay (I suspect that my files did not make it out of Manila until after I complained about it) my Oracle Certification Program Success Kit arrived today. It includes a certificate signed by a Senior Vice President (although if Oracle is like Citibank, where apparently everyone with his own desk is called VP, that is not as impressive as it sounds), a plastic card, the right to display the OCP logo on resume and web site, and a free subscription to Oracle Magazine. Interestingly, the certificate and card are dated at May 30, 2004 and the kit has been posted in Germany, even though I am Tokyo and the sender in Edina, Minnesota.

So what is next? There are four choices for me if I want to continue collecting Oracle credentials:

• 9i is no longer the latest Oracle version. 10g was released this year, and of course, there are upgrade paths to become a 10g OCP. This will get interesting once we start migrating to 10g, but for now we are still using 9i (even for new projects).
• While the OCP is only the middle tier in the certification track (above Associate, below Master), the road to Certified Master seems to be quite long: there are no more exams, but you are expected to have several years of experience in the field, attended two advanced courses at Oracle University and you have to complete a practicum.
• Probably because of this (or maybe because certification is a lucrative business), Oracle have introduced a step in between, the OCP Special Accreditation. The first (and at the moment only) choice here is Managing Oracle on Linux, which actually makes some sense for me, as half of our Oracle installations are on Linux (the other half is on Solaris).
• I addition to Certified Professional DBA, I am also a Certified Associate Developer. The developer track also has an OCP tier, and I see myself more as a developer than a DBA, but whereas the OCA exams where about PL/SQL (very useful), the OCP exam focuses on Oracle Forms, which is a technology I have so far had no contact with. I am not even sure what it is.

But now I have to go and print name cards with the new logo.

The logic of null values in Oracle is peculiar. For starters, an empty string is treated as a null value, a behaviour that is totally against the SQL standard and common sense. And comparison operators also behave in unexpected ways when they encounter nulls. A null value is never equal to anything else (makes sense). A null value is also never equal to another null value (stranger, but I can live with that). But apparently a null value is also never not equal (!=) to anything else (which I find very counter-intuitive):

SQL> select * from dual where null = 77;
no rows selected

SQL> select * from dual where null = null;
no rows selected

SQL> select * from dual where null != null;
no rows selected

SQL> select * from dual where null != 77;
no rows selected

This all adds up to a gaping security hole in my login password checking code:

if ( password != v_password) then
    events.count_it(-1); -- wrong password
    return -1;
end if;

Before I changed it to

if ( password is null or password != v_password) then
    events.count_it(-1); -- wrong password
    return -1;
end if;

people have been able to log in just by entering an empty password, which gets interpreted as NULL, which is never regarded as different from the real password.

I am very grateful that someone spotted this before we launched the site.

Just in time to make my quarterly performance review look a little more favourable, I cleared this last exam in the DBA track in my second attempt. I was much more confident with my answers to all those questions about database recovery than in the first time round, and most of them were correct, too. Still, I prefer not to have to exercise those new skills, recovering databases is a very stressful business.

Two years ago, I would have been able to call myself an Oracle Certified Professional now. Unfortunately, Oracle has in the meantime introduced a new requirement to attend one instructor-led hands-on course. I have to find out how to do that in Japan (in English), which is probably not such big a problem as the class can also be attended online somehow, and if there is a budget for me to take part in this rather expensive event.