Oracle数据库:如何读取BLOB? [英] Oracle database: How to read a BLOB?

问题描述

我正在使用Oracle数据库，并且我想阅读BLOB的内容.我该怎么做呢?

I'm working with an Oracle database, and I would like to read the contents of a BLOB. How do I do this?

当我执行一个简单的select语句时，它仅返回(BLOB)"(不带引号).如何阅读实际内容?

When I do a simple select statement, it merely returns "(BLOB)" (without the quotes). How do I read the actual contents?

推荐答案

您可以使用UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2())以十六进制格式转储值.

You can dump the value in hex using UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2()).

SELECT b FROM foo;
-- (BLOB)

SELECT UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(b))
FROM foo;
-- 1F8B080087CDC1520003F348CDC9C9D75128CF2FCA49D1E30200D7BBCDFC0E000000

这很方便，因为您使用的是与插入BLOB列相同的格式:

This is handy because you this is the same format used for inserting into BLOB columns:

CREATE GLOBAL TEMPORARY TABLE foo (
    b BLOB);
INSERT INTO foo VALUES ('1f8b080087cdc1520003f348cdc9c9d75128cf2fca49d1e30200d7bbcdfc0e000000');

DESC foo;
-- Name Null Type 
-- ---- ---- ---- 
-- B        BLOB 

但是，在某个特定点(2000字节?)，相应的十六进制字符串超过了Oracle的最大字符串长度.如果需要处理这种情况，则必须合并

However, at a certain point (2000 bytes?) the corresponding hex string exceeds Oracle’s maximum string length. If you need to handle that case, you’ll have to combine How do I get textual contents from BLOB in Oracle SQL with the documentation for DMBS_LOB.SUBSTR for a more complicated approach that will allow you to see substrings of the BLOB.

这篇关于Oracle数据库:如何读取BLOB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！