These are some of my favourite things

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.

Wish I’d read this first

I’ve been partially responsible for the creation of a new XML format for use at work. We’ve been working on it for around six months. It does the job- but it sure is ugly. A lot of that is because we didn’t have a nice set of standards to begin with. I wish I’d known about Google’s XML Document Format Style Guide six months ago.

The major things that leap out at me:

• Consistency. This is really grinding on me at the moment. Parts of the format are camelCase, parts are all lowercase, others just random. In parts we use venetian blind design, other places Russian doll.
• The build versus design argument: we built part, and borrowed a large chunk at the ninth hour (big part of the reason behind the inconsistency.)

I’m not completely sold that reusing an existing format would have been better. There are existing formats out there to deal with the type of data we’re using (financial services client/account details.) In this case, though, we face some unique constraints.

The schema is directly exposed to users at two levels. Firstly, through automatically generated forms from the off-the-shelf package we’re implementing. And secondly, behind the scenes, to BAs that support that package. The schema that we stole parts of was clearly not designed for this type of exposure. It focussed on machine-readability over human readability. We needed to do a lot of work to clean it up.

Updates

A few quick updates for keen readers: I know I haven’t been posting as regularly as I would like. There are a few things in the pipeline, but nothing brought to fruition as yet.

For those worried about whether or not my business case for multiple monitors was successful or not: it was indeed successful. My PC was swapped out and a second monitor installed on Thursday morning. So far, they are everything I hoped for and more.

I’m sure there is also great interest in how things are progressing with Phoenix, the idea of managing IT systems from cradle to cradle, rather than cradle to grave. Unfortunately, there has not been a great deal of activity on this front, but let me leave you with a qualification, a question, and the promise of more to come.

The qualification: Despite the reference to Cradle to Cradle, Phoenix isn’t about green IT systems – although they are very important. Instead, Phoenix is about how the information imbedded in one system survives the death of that system, and rebirth in another.

The question: if you’re developing new functionality with a system today, the odds are pretty good that XML is involved. Are we at the point where we have standardised building blocks for XML schemas? For example, physical mailing addresses: is there the one true XML schema for address information? Or are there a half a dozen competing standards, plus hundreds of unique implementations imbedded within larger frameworks?

The promise of more to come: there will be more to come.