Hey, this is Tom Kyte from Oracle's AskTom and The Tom Kyte Blog filling in for Alex Papadimoulis since he is on vacation.

After reviewing many of the historical (or is that hysterical?) database WTF's, I settled in on one that reminded me of an old AskTom thread. The original WTF is JOIN ON WTF, in all of its glory. The goal of the "architects" of that system was simply stated:

The database design will be fully extensible. All tables will have only a few columns (such as ID's and created dates), and the rest of the data will be stored in an XML-formatted TEXT column

I saw that and just knew what was coming next – the query from heck to try to pull anything out. The supplied query was great as it shows the simplicity of trying to join to XML instances!

I feel I can one up that though. At least XML is sort of an industry standard – there are after all many common tools that could actually process this data. Not that it would perform, not that data integrity would exist, not that it would be a good idea – but it beats this idea: Dynamic Query Containing Serialized Java Blob

There is the ultimate in extensibility! One table (no need to pester those mean DBAs asking them to create tables or indexes or anything). It can hold any object on the planet:

I have a table with a blob field, and type among other details. Say, 

CREATE TABLE trx (
        trxId NUMBER(18),
        trxType VARCHAR2(20),
        objValue BLOB,
        ...
    )

Blob Fields contains a java serialised object, different objects based on types, though all of them implements same interface. We access this object always thru' j2ee container, so it works fine so far.

Unfortunately, the "architects" of this system discovered that:

Now users want to use reports using sqlplus, crystal reports etc. So they want a solution to this blob issue.

Yes indeed, those dreaded end users actually wanted access to their data! Can you believe the gall of these end users.

It is funny that the last follow up on that thread (the java serialized objects one) was by someone named Alexander and it suggested using XML! Coincidence? Maybe ... we do know that Alex knows about the XML approach after all :-)

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!