1

I have a table that have a BLOB data type. I wanted to get the value of the BLOB and convert it to Varchar2 or string text readable format. I'm using the code below. The expected result is just a string varchar2. However it is returning special characters that I can't read. SQL:

 select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(MY_FIELD_BLOB_TYPE))
  from MY_BLOB_TABLE 
  where MY_BLOB_TABLE_ID = 12345
  ;

RESULT:

?p?C?.a?I?8gb?!??2f??mc?c?:0?:b?n?7i?1M9s????S?a?6gv?a]?y?m??-;???r??2n?B?20???S?4n??l?%d???EM?9 ?q???3 ?m??d? ???3e???[??M1o?n???m??'????9m??????%?`??;n???a?X?????7 >9v???|?m???n?9i???4H??o??k????N?.u???2xk?6y@??c?G??U????#B????????5~?Gc?$????My?u??b????????T???1fb?_??k?ek???wk Hi???S?t???>??&i?p??

Jemru
  • 2,091
  • 16
  • 39
  • 52
  • Possible duplicate of [How do I get textual contents from BLOB in Oracle SQL](http://stackoverflow.com/questions/828650/how-do-i-get-textual-contents-from-blob-in-oracle-sql) – a'r Sep 09 '16 at 10:45
  • 3
    So what **is** the expected output? –  Sep 09 '16 at 10:49
  • what are you storing in the BLOB? those are binary data types that don't have to contain text... – mmmmmpie Sep 09 '16 at 14:06
  • hi all, I'm expecting a string text output without RTF formatting. I can view the expected string text output using an 3rd party application which I can't see the codes. So the values are just plain string. – Jemru Sep 13 '16 at 04:37

2 Answers2

4

I can't guarantee that this works in your case, however, employing 2 things did the trick:

  • realizing that oracle can only handle 2000 characters at a time

  • being told that such a blob column is compressed

    select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(UTL_COMPRESS.LZ_UNCOMPRESS(MY_FIELD_BLOB_TYPE),2000,1)) from MY_BLOB_TABLE where MY_BLOB_TABLE_ID = 12345 ;

To get the next 2000 characters, you'd change the substr range:

utl_compress.lz_uncompress(MY_FIELD_BLOB_TYPE),2000,2001)
Jakob Ziegler
  • 101
  • 1
  • 3
3
SELECT utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(MY_FIELD_BLOB_TYPE)))))
FROM MY_BLOB_TABLE
WHERE MY_BLOB_TABLE_ID = 12345;

Result: P3A/Qz8uYT9JPzhnYj8hPz8yZj8/bWM/Yz86MD86Yj9uPzdpPzFNOXM/Pz8/Uz9h PzZndj9hXT95P20/Py07Pz8/cj8/Mm4/Qj8yMD8/P1M/NG4/P2w/JWQ/Pz9FTT85 ID9xPz8/MyA/bT8/ZD8gPz8/M2U/Pz9bPz9NMW8/bj8/P20/Pyc/Pz8/OW0/Pz8/ Pz8lP2A/PztuPz8/YT9YPz8/Pz83ID45dj8/P3w/bT8/P24/OWk/Pz80SD8/bz8/ az8/Pz9OPy51Pz8/MnhrPzZ5QD8/Yz9HPz9VPz8/PyNCPz8/Pz8/Pz81fj9HYz8k Pz8/P015P3U/P2I/Pz8/Pz8/P1Q/Pz8xZmI/Xz8/az9laz8/P3drIEhpPz8/Uz90 Pz8/Pj8/Jmk/cD8/

waldemort
  • 31
  • 6