SQL Oracle 数据库：如何读取 BLOB？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5682387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle database: How to read a BLOB?
提问by ktm5124
I'm working with an Oracle database, and I would like to read the contents of a BLOB. How do I do this?
我正在使用 Oracle 数据库，我想读取 BLOB 的内容。我该怎么做呢？
When I do a simple select statement, it merely returns "(BLOB)" (without the quotes). How do I read the actual contents?
当我执行一个简单的 select 语句时，它只返回“(BLOB)”（不带引号）。我如何阅读实际内容？
回答by andrewdotn
You can dump the value in hex using UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2()).
您可以使用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:
这很方便，因为这与用于插入 BLOB 列的格式相同：
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 SQLwith the documentation for DMBS_LOB.SUBSTRfor a more complicated approach that will allow you to see substrings of the BLOB.
但是，在某个时刻（2000 字节？），相应的十六进制字符串超过了 Oracle 的最大字符串长度。如果您需要处理这种情况，您必须将如何从 Oracle SQL 中的 BLOB 获取文本内容与文档结合起来，DMBS_LOB.SUBSTR以获得更复杂的方法，以便您查看 BLOB 的子字符串。
回答by bob coner
SQL Developer can show the blob as an image (at least it works for jpegs). In the Data view, double click on the BLOB field to get the "pencil" icon. Click on the pencil to get a dialog that will allow you to select a "View As Image" checkbox.
SQL Developer 可以将 blob 显示为图像（至少它适用于 jpeg）。在数据视图中，双击 BLOB 字段以获取“铅笔”图标。单击铅笔会出现一个对话框，您可以在其中选择“查看为图像”复选框。
回答by AchimHensel
If the content is not too large, you can also use
如果内容不是太大，也可以使用
SELECT CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) FROM <table>;
or
或者
SELECT DUMP ( CAST ( <blobfield> AS RAW( <maxFieldLength> ) ) ) FROM <table>;
This will show you the HEX values.
这将显示十六进制值。
回答by Dave Harper
If you use the Oracle native data provider rather than the Microsoft driver then you can get at all field types
如果您使用 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
The connection string does not require a Provider value so you would use something like:
连接字符串不需要 Provider 值，因此您可以使用以下内容：
"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
附上命令并设置Sql语句
cm.Connection = cn
cm.CommandText = strCommand
Set the Fetch size. I use 4000 because it's as big as a varchar can be
设置获取大小。我使用 4000 因为它和 varchar 一样大
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.SequentialAccessfor 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.
如果您首先确定列中的数据类型，您可以更具体地读取，例如 dr.GetOracleString(i) dr.GetOracleClob(i) 等。如果您正在阅读 LONG 数据类型，那么 simple dr(i)ordr.GetOracleString(i)工作正常。关键是确保 InitialLONGFetchSize 对数据类型来说足够大。另请注意，本机驱动程序不支持CommandBehavior.SequentialAccess数据读取器，但您不需要它，而且 LONG 字段甚至不必是 select 语句中的最后一个字段。
回答by TTT
What client do you use? .Net, Java, Ruby, SQLPLUS, SQL DEVELOPER? Where did you write that simple select statement?
你用什么客户端？.Net、Java、Ruby、SQLPLUS、SQL 开发人员？你在哪里写的那个简单的选择语句？
And why do you want to read the content of the blob, a blob contains binary data so that data is unreadable. You should use a clob instead of a blob if you want to store text instead of binary content.
以及为什么要读取 blob 的内容，blob 包含二进制数据，因此数据不可读。如果要存储文本而不是二进制内容，则应使用 clob 而不是 blob。
I suggest that you download SQL DEVELOPER: http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html. With SQL DEVELOPER you can see the content.
我建议您下载 SQL DEVELOPER：http: //www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html。使用 SQL DEVELOPER，您可以看到内容。
回答by 5ton3
If you're interested to get the plaintext (body part) from a BLOB, you could use the CTX_DOC package.
如果您有兴趣从 获取明文（正文部分）BLOB，您可以使用CTX_DOC 包。
For example, the CTX_DOC.FILTERprocedure can "generate either a plain text or a HTML version of a document". Be aware that CTX_DOC.FILTERrequires an index on the BLOBcolumn. If you don't want that, you could use the CTX_DOC.POLICY_FILTERprocedure instead, which doesn't require an index.
例如，该CTX_DOC.FILTER过程可以“生成文档的纯文本或 HTML 版本”。请注意，这CTX_DOC.FILTER需要BLOB列上的索引。如果您不希望那样，您可以改用CTX_DOC.POLICY_FILTER不需要索引的过程。
