[sql] Oracle database: How to read a BLOB?

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

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

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.

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.

What client do you use? .Net, Java, Ruby, SQLPLUS, SQL DEVELOPER? Where did you write that simple select statement?

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.

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.

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.

If you're interested to get the plaintext (body part) from a BLOB, you could use the CTX_DOC package.

For example, the CTX_DOC.FILTER procedure can "generate either a plain text or a HTML version of a document". Be aware that CTX_DOC.FILTER requires an index on the BLOB column. If you don't want that, you could use the CTX_DOC.POLICY_FILTER procedure instead, which doesn't require an index.

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.