Blog “Feed” “Syndication”

Thomas from COS (is that Colorado Springs?) asks if I can publish the blog in a feed.

By using a combination of dictionary and documentation I understand that there is some type of feed going on from http://oraclesponge.blogspot.com/atom.xml but that it might not be as compatible as it might be. I’ve signed up for a hopefully-money-and-spam-free service that “syndicates” the “feed” through a “website” apparantly … http://feeds.feedburner.com/OracleSponge

Let me know how that works out for you feed-reading technoliterati.

Comments (the type you get in code, that is)

“Supposed” to get, that is. What is it with SQL and comments? Why do we so rarely see explanatory comments in SQL code? Or even a decent structure?

One of the features of front-end tools like Business Objects or Microstrategy is their ability to allow the designer to create extraordinarily long and complex looking SQL with ease. (If you don’t use such tools then don’t go away — I’ll deal with you in a moment). It starts by having an object or two that are pretty simple, then you add them together … then you divide by another two objects added together, but then you need a decode on the divisor to change zero’s to null’s so that the SQL doesn’t error out, and pretty soon you’ve got an expression with a length of 1000 characters that is unreadable to the human eye.

Of course it’s not just extremely long expressions that are tricky … sometimes it’s to look at a piece of SQL even to find out which simple expression relates to which object in the universe/project/whatever. So do me a favour and put a comment block in the definitions … something like …

Sum(order_amt)/Decode(Sum(order_qty),0,Null,Sum(order_qty)) /* “Average Unit Cost” */

… and it’ll appear in the SQL itself. Not too tricky, eh?

You know, I do have a method for doing this automatically by hacking a Business Objects repository with an update statement, but if I told you how to do that and you fkd it up then you’d probably sue me or something.

By the way, the same thing applies to join definitions.

Moving along … if you’re hand-coding SQL statement then you have even less excuse for not commenting your SQL. If you follow the principle that it is better to do things in SQL than PL/SQL then you’re probably packing a fair amount of functionality into each statement, and that means …

• Write a header to say what the hell the SQL is supposed to do … doesn’t have to be in the SQL itself, just above it will do nicely. In fact, work out this statement even before you write the SQL itself and you’ll keep a more clear mind when you are writing the code. Include in that header how it is going to do it — remember that complex SQL packs a huge amount of functionality into a small amount of text — it’s much more “logically dense” than regular 3GL code. TK has an example of this here, and thanks for the suggestion to include this, Tom.
• Maybe you might even like to put in a comment about performance, eh? “Runs in about 0.2 seconds” … “Uses a lot of hash joins” … etc
• Put a statement inside the SQL to say where in the code it is located (package/procedure). You wouldn’t inflict a twenty-page procedure on anyone so it’ll be easy to find.
• You might even like to put in a unique identifier for the SQL as well as it’s location (for example, so that it’s easy to reference the copy of a “good” explain plan that you’ve kept for that query, in case of problems down the road).
• Format the code nicely
• NEW ITEM: if you are referencing any view more complex than “SELECT blah from whatever”, or if any of the tables are accessed remotely through a db link that is obscured by a synonym, or is actually the query name for a subquery factoring clause, then a comment would be appropriate. And when I say “appropriate” I mean just do it.
• Clearly seperate the joins from the filters
• Any tricky looking expressions, state what they do. Especially analytic functions and subquery factors (“what they?“)
• Document anything that looks funky (if you are using “… and rownum > 1″ in an in-line view to stop the optimizer from pushing predicates then say so … not everyone is as smart as you are).
• (Except Tom Kyte (“who he?“), who tells me that he’s perfect. My opinion: man, has he aged since his photo in “Expert One On One”. Am I right, ladies?).
• Erm …
• For the love of God, use table aliases. Friendly ones, not a, b, and c.
• Just use your common sense. People have to support that code
• That’s it

Back to work, fellas.