Oracle database How to read a BLOB

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If the content is not too large  you can also use  SELECT CAST    lt blobfield gt  AS RAW   lt maxFieldLength gt      FROM  lt table gt     or   SELECT DUMP   CAST    lt blobfield gt  AS RAW   lt maxFieldLength gt        FROM  lt table gt     This will show you the HEX values

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to blob