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

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

以下查询将允许您查看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>';

SQL Developer也提供了此功能:

双击“结果”网格单元,然后单击“编辑”:

就这样!

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;

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;

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

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

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

巴恩的回答对我来说是有修改的,因为我的专栏没有被压缩。快速而肮脏的解决方案:

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

我为此挣扎了一段时间,并实现了PL/SQL解决方案,但后来意识到,在Toad中,您只需双击结果网格单元格,它就会打开一个包含文本内容的编辑器。(我在看蟾蜍v11)

docs ):

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

这对我来说在Oracle19c中是有效的,它有一个大于VARCHAR限制的BLOB字段。我获得可读文本(来自一个JSON的BLOB)

您可以尝试以下方法:

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

如果使用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;
/

然后运行select以获取文本

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

为我工作,

选择lcase((插入( 插入( 插入(十六进制(BLOB_字段),9,0',-'), 14,0,'-'), 19,0,'-'), 24,0,“-”))作为字段ID 从带有\u BLOB的表\u

使用 TO_CHAR 作用

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

皈依 NCHAR , NVARCHAR2 , CLOB 或 NCLOB 将数据添加到数据库字符集。返回的值始终为 VARCHAR2