Aiming for elegance, one thought at a time

These are some of my favourite things

Posted: April 2nd, 2010 | Author: | Filed under: IT | No Comments »

Over the past year, I’ve spent a lot of time extracting and manipulating data in Oracle databases. Powerful things, them. These are some of the small-ticket, but kinda cool, features that I’ve found useful – the type of thing that doesn’t make the sales brochures, but can save time when you need it.

wm_concat
wm_concat is an unsupported string aggregate function, so it’s not often mentioned. In a grouping query, wm_concat will concatenate up to 255 (I believe) string values, and return a comma separated list. I used wm_concat when I had a table of operations that could be linked to multiple errors, and I wanted a summary of the most common combinations. You can achieve the same thing with a user defined aggregate function, but it’s nice that it’s just built in (unless the DBAs have disabled it.)

xmlelement, xmlforest, xmlagg
Need to get xml out of your database? Sure you do. Yes, you could write something in whatever language, or better yet, use a case tool to autogen that code, but it’s pretty neat to get it straight from the DB. xmlelement, predicably, takes some parameters and makes an xml element. xmlforest returns a whole bunch of elements. xmlagg is an aggregation function to wrap a number of rows up together. You can combine these three functions (plus there are others) and build some very complex xml. The downside: you get a query that’s really not pretty. These function are part of the SQL/XML standard, which seems to have pretty much languished since 2003. Anyone using this in a production environment?

case statements in sql
Case statements within sql queries are ugly (they break with the sql paradigm – but maybe it’s the SQL that’s ugly, and the case statement just brings that home?) but they sure are useful. They can be easier to understand than decode() or some of the more creative hacks combining sign() and other functions in ways that were never intended. So I guess it’s not all bad.
Tags: , , , , ,
Sign up for danielstudds.com
* = required field

Leave a Reply