Oracle handy BLOB to VARCHAR2 conversion

I do love Oracle for the things it does incredibly well under the hood, but it continues to amaze me how hard it is to do the simple things.

We've got a blob column in our database that we used to store settings in an XML format.  Yeah it could have been a CLOB, but that's not the way it was done.  No big deal right?  I'll just cast it as a VARCHAR2.

SELECT CAST( XML AS VARCHAR2)
FROM USER_CONFIG

Nope.  Casting doesn't understand blobs.  You have to use DBMS_LOB.SUBSTR

SELECT DBMS_LOB.SUBSTR( XML, 4000, 1)
FROM USER_CONFIG

Nope again.  That gives me a return value of '123oij21o3ij12o3ij12o3ij12o3ij12'... the binary representation of my XML file.  Why?  Because DBMS_LOB assumes you'd never use a BLOB for a string.  CLOBs are for strings!  You can only convert BLOBs to RAWs.

Fine, fine.  I'll just cast the RAW into a VARCHAR2.

Of course, you can't just cast it using CAST... you have to use the UTL_RAW package.

SELECT UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR( XML, 4000, 1 ))
FROM USER_CONFIG

Love it!

I'm not sure a blog is the place to put all this, but I do play with an awful lot of neat stuff (well I think it's neat)... and I have a rotten memory, so I love documenting what I've worked on, so future-Jeff can find it.

I like to think of myself as a can-do guy, but at the end of the day, I want to feel good about my code.  Well written code pays dividends over and over in maintainability and expandability.  

I'm a huge believer in loose-coupling.  Each code block should have the least possible number of dependencies.  You don't need goto's to produce spaghetti code, all you need is a high number of dependencies.

My current interests:

• .Net 
• Web applications
• CSS
• jQuery
• Model driven design.  I'm currently learning Eric Evans' "Domain Driven Design" patterns, but more generic than that, I believe the model layer (or business layer) should be the driving force behind application development.
• Unit testing - I believe in testing all code in the smallest units possible.
• Design by contract
• Relational databases (Oracle, Sqlite, MsSql, MySql)
• Database optimization.