Oracle Gotchas
Let me start by saying that, as a relational database management system, I like Oracle. It’s full-featured, mature, and very scalable. However, there are a few small areas where I have to wonder what the developers were thinking. I’ve been working with Oracle for just over a year now. While I’m no self-proclaimed authority on the subject, I thought I’d document my thoughts and see if anyone else shared my mind or had found annoyances in other areas. There may be good design decisions behind these situations, but from a usability perspective, they’re blemishes that make Oracle slightly less than pristine.
Different rules for SQL and PL/SQL when using duplicate placeholders in dynamic SQL
Why is this? You don’t have to specify variable bindings multiple times for the same variable in PL/SQL, but you do in SQL. Why not just remove duplication from both cases?
SQL doesn’t support functions returning BOOLEAN values
See the Note under the RETURN Clause section on the CREATE FUNCTION page. (A rather obscure place to make the note, I might add.) Granted, this is an easily remedied situation by using wrapper functions to return equivalent values using other data types, but it shouldn’t have to be remedied at all. It discourages good practices in selecting return data types for functions and, by proxy, needless complexity. Boolean values can obviously be conceptually understood or we wouldn’t have conditional expressions. Would it really be so hard to support implicit or explicit use of boolean return values as well?
PL/SQL doesn’t support the DECODE function
The DECODE function is essentially the functional equivalent of a CASE expression. Granted, CASE statements are more easily readable, but at the cost of brevity. There are instances where use of a full-blown CASE statement seems unwarranted and DECODE would be a perfect fit, but can’t be used. It makes the claim of “tight integration with SQL” seem a little less accurate.
Rules for handling NULL are F.U.B.A.R.
This one probably deserves a whole post by itself, which I’ll probably get around to writing in the near future. Suffice it to say, if you’ve worked with Oracle before, you probably know what I’m talking about.
No Exclusive OR Operator
See for yourself. The basic operators (AND, OR, and NOT) are there. NAND and NOR are derived easily enough by simply using NOT to negate AND and OR respectively. Lack of an XOR operator requires either writing a userland function to do the job or replicating at least one condition in your SQL query at least once. There are other options like using the bitand function, but they lack the level of readability that an XOR expression would have. I honestly can’t imagine that it would be so difficult to implement, either. The only difference between it and the existing OR operator is that the XOR operator returns false instead of true when both operands evaluate to true.
No LIMIT clause support
Several other databases including MySQL, PostgreSQL, and SQLite have it, but commercial databases like Oracle, Microsoft SQL Server, and IBM DB2 don’t. It’s hard to understand why, because it’s really not so complex a feature. It can even optimize runtime in cases where the desired subset starts at the beginning of the result set. Granted, the same effect can be accomplished for each of these, but the syntax is convoluted and unintuitive. For Oracle in particular, a subquery and use of a dynamically determined pseudocolumn ROWNUM is required. If the original query has a GROUP BY clause, then two subquery layers are required. Whether the underlying engine would process it the same way or not, the simple addition of this clause would make the feature easier and more intuitive to implement for developers.
Wrap Up
Despite these small deficiencies, Oracle is an excellent database. The recent additions of Oracle XE and Oracle SQL Developer also make it very easy to get started as a developer. I recommend the SQL Reference and PL/SQL Reference as beginning references. See the Oracle Technology Network for further references.
