如何从 Oracle SQL 中的 BLOB 获取文本内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/828650/
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
How do I get textual contents from BLOB in Oracle SQL
提问by Roland Tepp
I am trying to see from an SQL console what is inside an Oracle BLOB.
我试图从 SQL 控制台查看 Oracle BLOB 中的内容。
I know it contains a somewhat large body of text and I want to just see the text, but the following query only indicates that there is a BLOB in that field:
我知道它包含的文本体量有点大，我只想查看文本，但以下查询仅表明该字段中有一个 BLOB：
select BLOB_FIELD from TABLE_WITH_BLOB where ID = '<row id>';
the result I'm getting is not quite what I expected:
我得到的结果并不完全符合我的预期：
BLOB_FIELD
-----------------------
oracle.sql.BLOB@1c4ada9
So what kind of magic incantations can I do to turn the BLOB into it's textual representation?
那么我可以做什么样的魔法咒语来将 BLOB 变成它的文本表示呢？
PS: I am just trying to look at the content of the BLOB from an SQL console (Eclipse Data Tools), not use it in code.
PS：我只是想从 SQL 控制台（Eclipse Data Tools）查看 BLOB 的内容，而不是在代码中使用它。
回答by Mac
First of all, you may want to store text in CLOB/NCLOB columns instead of BLOB, which is designed for binary data (your query would work with a CLOB, by the way).
首先，您可能希望将文本存储在 CLOB/NCLOB 列中，而不是 BLOB，后者专为二进制数据而设计（顺便说一下，您的查询将使用 CLOB）。
The following query will let you see the first 32767 characters (at most) of the text inside the blob, provided all the character sets are compatible (original CS of the text stored in the BLOB, CS of the database used for VARCHAR2) :
以下查询将让您看到 blob 中文本的前 32767 个字符（最多），前提是所有字符集都兼容（存储在 BLOB 中的文本的原始 CS，用于 VARCHAR2 的数据库的 CS）：
select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';
回答by Imran Patel
You can use below SQL to read the BLOB Fields from table.
您可以使用下面的 SQL 从表中读取 BLOB 字段。
SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;
回答by nullPointer
SQL Developer provides this functionality too :
SQL Developer 也提供此功能：
Double click the results grid cell, and click edit :
双击结果网格单元格，然后单击编辑：
Then on top-right part of the pop up , "View As Text" (You can even see images..)
然后在弹出窗口的右上角，“以文本形式查看”（您甚至可以看到图像..）
And that's it!
就是这样！
回答by Barn
If you want to search inside the text, rather than view it, this works:
如果你想在文本中搜索，而不是查看它，这有效：
with unzipped_text as (
select
my_id
,utl_compress.lz_uncompress(my_compressed_blob) as my_blob
from my_table
where my_id='MY_ID'
)
select * from unzipped_text
where dbms_lob.instr(my_blob, utl_raw.cast_to_raw('MY_SEARCH_STRING'))>0;
回答by Sonic Soul
I struggled with this for a while and implemented the PL/SQL solution, but later realized that in Toad you can simply double click on the results grid cell, and it brings up an editor with contents in text. (i'm on Toad v11)
我为此苦苦挣扎了一段时间并实施了 PL/SQL 解决方案，但后来意识到在 Toad 中您只需双击结果网格单元格，它就会显示一个包含文本内容的编辑器。（我在 Toad v11 上）
回答by Pecos Bill
Barn's answer worked for me with modification because my column is not compressed. The quick and dirty solution:
Barn 的答案经过修改对我有用，因为我的专栏没有被压缩。快速而肮脏的解决方案：
select * from my_table
where dbms_lob.instr(my_UNcompressed_blob, utl_raw.cast_to_raw('MY_SEARCH_STRING'))>0;
回答by Swapnil Ingle
Use this SQL to get the first 2000 chars of the BLOB.
使用此 SQL 获取 BLOB 的前 2000 个字符。
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(<YOUR_BLOB_FIELD>,2000,1)) FROM <YOUR_TABLE>;
Note:This is because, Oracle will not be able to handle the conversion of BLOB that is more than length 2000.
注意：这是因为，Oracle 将无法处理长度超过 2000 的 BLOB 的转换。
回答by Arsen Salamakha
In case your text is compressed inside the blob using DEFLATE algorithm and it's quite large, you can use this function to read it
如果您的文本使用 DEFLATE 算法在 blob 中压缩并且非常大，您可以使用此函数读取它
CREATE OR REPLACE PACKAGE read_gzipped_entity_package AS
FUNCTION read_entity(entity_id IN VARCHAR2)
RETURN VARCHAR2;
END read_gzipped_entity_package;
/
CREATE OR REPLACE PACKAGE BODY read_gzipped_entity_package IS
FUNCTION read_entity(entity_id IN VARCHAR2) RETURN VARCHAR2
IS
l_blob BLOB;
l_blob_length NUMBER;
l_amount BINARY_INTEGER := 10000; -- must be <= ~32765.
l_offset INTEGER := 1;
l_buffer RAW(20000);
l_text_buffer VARCHAR2(32767);
BEGIN
-- Get uncompressed BLOB
SELECT UTL_COMPRESS.LZ_UNCOMPRESS(COMPRESSED_BLOB_COLUMN_NAME)
INTO l_blob
FROM TABLE_NAME
WHERE ID = entity_id;
-- Figure out how long the BLOB is.
l_blob_length := DBMS_LOB.GETLENGTH(l_blob);
-- We'll loop through the BLOB as many times as necessary to
-- get all its data.
FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP
-- Read in the given chunk of the BLOB.
DBMS_LOB.READ(l_blob
, l_amount
, l_offset
, l_buffer);
-- The DBMS_LOB.READ procedure dictates that its output be RAW.
-- This next procedure converts that RAW data to character data.
l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);
-- For the next iteration through the BLOB, bump up your offset
-- location (i.e., where you start reading from).
l_offset := l_offset + l_amount;
END LOOP;
RETURN l_text_buffer;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM,1,247));
END;
END read_gzipped_entity_package;
/
Then run select to get text
然后运行选择以获取文本
SELECT read_gzipped_entity_package.read_entity('entity_id') FROM DUAL;
Hope this will help someone.
希望这会帮助某人。
回答by Reza Rahimi
You can try this:
你可以试试这个：
SELECT TO_CHAR(dbms_lob.substr(BLOB_FIELD, 3900)) FROM TABLE_WITH_BLOB;
However, It would be limited to 4000 byte
但是，它将被限制为 4000 字节
回答by Narendra Kalekar
Worked for me,
对我来说有效，
select lcase((insert( insert( insert( insert(hex(BLOB_FIELD),9,0,'-'), 14,0,'-'), 19,0,'-'), 24,0,'-'))) as FIELD_ID from TABLE_WITH_BLOB where ID = 'row id';
选择 lcase((插入(插入(插入(插入(插入(十六进制(BLOB_FIELD),9,0,'-'), 14,0,'-'), 19,0,'-'), 24,0,'- '))) 作为 TABLE_WITH_BLOB 中的 FIELD_ID，其中 ID = 'row id';
