如何从 Oracle SQL 中的 BLOB 获取文本内容

我试图从一个 SQL 控制台看到什么是一个 Oracle BLOB 内。

我知道它包含大量的文本，我只想看到文本，但下面的查询只表明该字段中有一个 BLOB:

select BLOB_FIELD from TABLE_WITH_BLOB where ID = '<row id>';

我得到的结果与我预期的不太一样:

BLOB_FIELD
-----------------------
oracle.sql.BLOB@1c4ada9

那么，我能用什么样的魔法咒语将 BLOB 转换成它的文本表示呢？

PS: 我只是试图从 SQL 控制台(Eclipse Data Tools)查看 BLOB 的内容，而不是在代码中使用它。

561786 次浏览
小开
最佳答案

首先，您可能希望在 CLOB/NCLOB 列中存储文本，而不是为二进制数据设计的 BLOB (顺便说一下，您的查询可以使用 CLOB)。

如果所有字符集都是兼容的(存储在 BLOB 中的文本的原始 CS，用于 VARCHAR2的数据库的 CS) ，下面的查询将让您看到 BLOB 中文本的前32767个字符(最多) :

select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';
小开

使用 TO_CHAR函数。

select TO_CHAR(BLOB_FIELD) from TABLE_WITH_BLOB where ID = '<row id>'

将 NCHAR、 NVARCHAR2、 CLOB或 NCLOB数据转换为数据库字符集。返回的值总是 VARCHAR2。

小开

如果你想在文本内部搜索，而不是查看它，这是可行的:

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;
小开

巴恩的回答对我很有效，因为我的专栏没有被压缩，快速而肮脏的解决方案是:

select * from my_table
where dbms_lob.instr(my_UNcompressed_blob, utl_raw.cast_to_raw('MY_SEARCH_STRING'))>0;
小开

我为此挣扎了一段时间，并实现了 PL/SQL 解决方案，但后来意识到，在 Toad 中，您可以简单地双击结果网格单元格，它会显示一个带有文本内容的编辑器。(我在蟾蜍 v11)

enter image description here

小开

可以使用下面的 SQL 从表中读取 BLOB 字段。

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;
小开

你可以试试这个:

SELECT TO_CHAR(dbms_lob.substr(BLOB_FIELD, 3900)) FROM TABLE_WITH_BLOB;

但是，它将被限制在4000字节

小开

对我有用,

选择 lcase ((插入( 插入( 插入( 插入(十六进制(BLOB _ FIELD) ，9,0,’-’) , 14,0,’-’) , 19,0,’-’) , ’-’))作为 FIELD _ ID 来自 TABLE _ WAS _ BLOB 其中 ID = ‘ row ID’;

小开

SQL Developer 也提供了这个功能:

双击结果网格单元格，然后单击编辑:

enter image description here

然后在弹出窗口的右上角，“以文本形式查看”(你甚至可以看到图像. .)

enter image description here

就是这样！

enter image description here

小开

如果文本在 blob 中使用 DEFLATE 算法进行压缩，而且文本很大，那么可以使用这个函数来读取文本

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;
/

然后运行 select 获取文本

SELECT read_gzipped_entity_package.read_entity('entity_id') FROM DUAL;

希望这能帮到别人。

小开

使用此 SQL 获取 BLOB 的前2000个字符。

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(<YOUR_BLOB_FIELD>,2000,1)) FROM <YOUR_TABLE>;

注意: 这是因为 Oracle 无法处理长度超过2000的 BLOB 的转换。

小开

我可以使用 TO _ CLOB (医生)使其工作:

select
to_clob(BLOB_FIELD)
from
TABLE_WITH_BLOB
where
ID = '<row id>';

这在 Oracle 19c 中适用，使用的 BLOB 字段更大，VARCHAR 限制更大。我得到了可读的文本(来自保存 JSON 的 BLOB)


提交答案