Campaign against incorrect data types

Those that have met me know that beneath that benign, placid exterior lurks a data-geek of great passion. Show me the work of others where they reinvent the wheel by writing their own procedural methods (in PL/SQL or T-SQL or whatever) to replicate a native function of the database or store numbers as characters, or dates as strings and I degenerate into rant mode. Hiding the true nature of data denies the database performance; the query optimiser no longer can say that 5th July 2007 is the day following 4th July 2007 if the dates are just a sequence of characters, and the implicit and explicit conversion of data types that wreaks havoc with the use of indexes for performance gain.

But what really gets my goat is the use of the Oracle LONG data type to store big lumps of text. As that seaside puppet anti-hero, Punch, could have said: "That's the way [NOT] to do it!"

Today I have been looking at a badly-designed third party database that as the ability to duplicate records, which oddly does not matter to the application as both records have unique ids based on a sequence, but does matter to an interface table that uses the latest datestamp and the parent id. Ideally the vendor should fix the code, but for now support (my people) needs to remove the duplicate. And like all good support people we want to keep the original data just in case we make a mistake. It's a shame that:

Create TABLE  MY_CPY_TABLE  as
SELECT * from SILLY_LONG_TABLE
where ITEM_ID = 1234567;

Just does not work.