如何在Oracle SQL中从BLOB获取文本内容 [英] How do I get textual contents from BLOB in Oracle SQL

问题描述

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

I am trying to see from an SQL console what is inside an Oracle BLOB.

我知道它包含一些较大的文本，我只想看一下文本，但是以下查询仅表明该字段中存在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

那么我可以做什么样的魔咒来将BLOB变成文本表示形式呢?

So what kind of magic incantations can I do to turn the BLOB into it's textual representation?

PS:我只是想从SQL控制台(Eclipse数据工具)查看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.

推荐答案

首先，您可能希望将文本存储在CLOB/NCLOB列中，而不是将BLOB用于二进制数据(您的查询将与CLOB一起使用) ，顺便说一句.)

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

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

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

这篇关于如何在Oracle SQL中从BLOB获取文本内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！