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!
