SQL ORACLE：如何将 BLOB 转换为 VARCHAR2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39409519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE: How to convert BLOB to VARCHAR2
提问by Jemru
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:
我有一个具有 BLOB 数据类型的表。我想获取 BLOB 的值并将其转换为 Varchar2 或字符串文本可读格式。我正在使用下面的代码。预期的结果只是一个字符串 varchar2。但是它返回了我无法阅读的特殊字符。查询语句：
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??
?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??
回答by waldemort
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/
结果：P3A / Qz8uYT9JPzhnYj8hPz8yZj8 / BWM / Yz86MD86Yj9uPzdpPzFNOXM / Pz8 / Uz9h PzZndj9hXT95P20 / Py07Pz8 / cj8 / MM4 / Qj8yMD8 / P1M / NG4 / P2W / JWQ / Pz9FTT85 ID9xPz8 / MYA / BT8 / ZD8gPz8 / M2U / Pz9bPz9NMW8 / BJ8 / P20 /比重计/ 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/
回答by Jakob Ziegler
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 ;
- 意识到 oracle 一次只能处理 2000 个字符
被告知这样的 blob 列已被压缩
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:
要获得接下来的 2000 个字符，您需要更改 substr 范围：
utl_compress.lz_uncompress(MY_FIELD_BLOB_TYPE),2000,2001)
