Monday, November 25, 2013

Reading BLOB as hexadecimal varchar string in Oracle DB

In a unnerving twist of fate, I had to check through the database for blobs matching a specific hex string "\u0020" aka the space character. After numerous searches, I concluded with the following that worked for me. Do note that there is still a 32KB limit on the final character string, so this works only if your blob is as small as what I'm expecting for mine.

SELECT recordid, DBMS_LOB.substr(blobunicode, length(blobunicode), 1) b64hex FROM tblblobjob WHERE length(blobunicode) BETWEEN 1 AND 10) AND DBMS_LOB.substr(blobunicode, length(blobunicode), 1) LIKE '20%'

In explanation, the DBMS_LOB package reads what it can grab from the blob column and produces a readable string from the base64 content. After reading several articles, I'd previously attempted to convert the BLOB to RAW, then to hexadecimal via the UTL_ENCODE package and whatnot. As it turns out, the solution was much simpler than visualised.

No comments:

Post a Comment