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

我试图从 SQL 控制台查看 Oracle BLOB 中的内容.

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

select BLOB_FIELD from TABLE_WITH_BLOB where ID = '';

我得到的结果并不完全符合我的预期:

<前>BLOB_FIELD-----------------------oracle.sql.BLOB@1c4ada9

那么我可以做什么样的魔法咒语才能将 BLOB 变成它的文本表示?

PS:我只是想从 SQL 控制台(Eclipse Data Tools)查看 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 = '';

I am trying to see from an SQL console what is inside an 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:

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?

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.

解决方案

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).

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) :

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