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!