当前位置：文江博客话题详情

SQL blob oracle-database

Oracle数据库：如何读取BLOB？

发布于 2024-11-01 11:04:04 字数 104 浏览 4 评论 0 原文

我正在使用 Oracle 数据库，并且我想读取 BLOB 的内容。我该怎么做？

当我执行一个简单的 select 语句时，它仅返回“(BLOB)”（不带引号）。如何阅读实际内容？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

影子是时光的心 2024-11-08 11:04:04

您可以使用 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 列的格式相同：

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

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

但是，在某个点（2000 字节？），相应的十六进制字符串超过了 Oracle 的最大字符串长度。如果您需要处理这种情况，则必须结合如何使用 href="http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS66748" rel="nofollow noreferrer">DMBS_LOB.SUBSTR 的文档更复杂的方法将允许您查看 BLOB 的子字符串。

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

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

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.

回复收藏 0 原文

血之狂魔 2024-11-08 11:04:04

SQL Developer 可以将 blob 显示为图像（至少它适用于 jpeg）。在数据视图中，双击 BLOB 字段以获取“铅笔”图标。单击铅笔将出现一个对话框，您可以在其中选择“查看为图像”复选框。

回复收藏 0 原文

寒尘 2024-11-08 11:04:04

如果内容不是太大，您还可以使用

SELECT CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) FROM <table>;

或

SELECT DUMP ( CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) ) FROM <table>;

这将显示十六进制值。

If the content is not too large, you can also use

SELECT CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) FROM <table>;

SELECT DUMP ( CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) ) FROM <table>;

This will show you the HEX values.

回复收藏 0 原文

且行且努力 2024-11-08 11:04:04

如果您使用 Oracle 本机数据提供程序而不是 Microsoft 驱动程序，那么您可以获取所有字段类型

Dim cn As New Oracle.DataAccess.Client.OracleConnection
Dim cm As New Oracle.DataAccess.Client.OracleCommand
Dim dr As Oracle.DataAccess.Client.OracleDataReader

连接字符串不需要提供程序值，因此您可以使用类似以下内容的内容：

"Data Source=myOracle;UserID=Me;Password=secret"

打开连接：

cn.ConnectionString = "Data Source=myOracle;UserID=Me;Password=secret"
cn.Open()

附加命令并设置 Sql 语句

cm.Connection = cn
cm.CommandText = strCommand

Set获取大小。我使用 4000 因为它和 varchar 一样大启动

cm.InitialLONGFetchSize = 4000

读取器并循环遍历记录/列

dr = cm.ExecuteReader

Do while dr.read()
    strMyLongString = dr(i)
Loop

您可以更具体地读取，例如 dr.GetOracleString(i) dr.GetOracleClob(i) 等。如果您首先确定列中的数据类型。如果您正在读取 LONG 数据类型，那么简单的 dr(i) 或 dr.GetOracleString(i) 就可以正常工作。关键是确保 InitialLONGFetchSize 对于数据类型来说足够大。另请注意，本机驱动程序不支持数据读取器的 CommandBehavior.SequentialAccess，但您不需要它，而且 LONG 字段甚至不必是 select 语句中的最后一个字段。

If you use the Oracle native data provider rather than the Microsoft driver then you can get at all field types

Dim cn As New Oracle.DataAccess.Client.OracleConnection
Dim cm As New Oracle.DataAccess.Client.OracleCommand
Dim dr As Oracle.DataAccess.Client.OracleDataReader

The connection string does not require a Provider value so you would use something like:

"Data Source=myOracle;UserID=Me;Password=secret"

Open the connection:

cn.ConnectionString = "Data Source=myOracle;UserID=Me;Password=secret"
cn.Open()

Attach the command and set the Sql statement

cm.Connection = cn
cm.CommandText = strCommand

Set the Fetch size. I use 4000 because it's as big as a varchar can be

cm.InitialLONGFetchSize = 4000

Start the reader and loop through the records/columns

dr = cm.ExecuteReader

Do while dr.read()
    strMyLongString = dr(i)
Loop

You can be more specific with the read, eg dr.GetOracleString(i) dr.GetOracleClob(i) etc. if you first identify the data type in the column. If you're reading a LONG datatype then the simple dr(i) or dr.GetOracleString(i) works fine. The key is to ensure that the InitialLONGFetchSize is big enough for the datatype. Note also that the native driver does not support CommandBehavior.SequentialAccess for the data reader but you don't need it and also, the LONG field does not even have to be the last field in the select statement.