Read Oracle BLOB field

Question

I try to read an Oracle BLOB field and show the content i a richTextBox. The examples i find with google are almost the same but still i can't get it to work. I know that the BLOB field contains serialized data. This is what i have so far: (the connecetion en reader work fine)

private void button1_Click_1(object sender, EventArgs e)
        {
            //testen of een blob is uit te lezen


            OracleCommand cmd = new OracleCommand();
            cmd.Connection = OraConnection.conn;

            cmd.CommandText = "select id, blobfield from test_table where id = '20ED7EDB-406A-43E8-945B-5E63DFCBA7FF'";
            cmd.CommandType = CommandType.Text;

            OracleDataReader dr = cmd.ExecuteReader();

            dr.Read();
            OracleBlob BLOB1 = dr.GetOracleBlob(1);

            Byte[] Buffer = (Byte[])(dr.GetOracleBlob(1)).Value;

            string lookupValue = System.Text.ASCIIEncoding.ASCII.GetString(Buffer);

            richTextBox1.Text += lookupValue;  //shows: DQStream
            richTextBox1.Text += "";
            richTextBox1.Text += "1";
            richTextBox1.Text += dr.GetOracleBlob(1).Value;  //shows: System.Byte[]
            richTextBox1.Text += "";
        }

I think you need to do this first: `OracleBlob blob = (OracleBlob)dr.GetOracleBlob(1);` — Glenn Ferrie, Jan 31 '17 at 19:47
You don't seem to be using `Blob1`. I just posted some sample code. — Glenn Ferrie, Feb 07 '17 at 13:19
A BLOB *is* a byte array. You can't assign it to a string. What did you expect? Did you confuse BLOB with CLOB (a large string) perhaps? In any case you **can't** just assign RTF text to the `Text` property. RTF is a text format, not serialized data. You'll end up displaying the raw file contents, font names and all. — Panagiotis Kanavos, Feb 07 '17 at 13:24
I came back to this question via Google. I ran into the same issue myself. — Glenn Ferrie, Feb 07 '19 at 16:09

Glenn Ferrie · Answer 1 · 2020-09-07T13:57:31.657

7

OracleBlob is a Stream -- it inherits Stream.

OracleBlob b = dr.GetOracleBlob(1); 
var sr = new System.IO.StreamReader(b);
var content = sr.ReadToEnd();

You should be able to get the data this way.

Large blocks of data are typically delivered as a stream.

Oracle Docs: OracleBlob https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleBlobClass.htm

EDIT If you want to cast it as byte[], then try this:

Byte[] buffer = (Byte[])(dr.GetOracleBlob(1)).Value; 
var content = new String(Encoding.UTF8.GetChars(buffer));

edited Sep 07 '20 at 13:57

answered Feb 07 '17 at 13:17

Glenn Ferrie

10,290
3
42
73

It also has a `Value` property that returns all the data as a byte array. That's not the problem. The problem is that the OP is trying to assign the byte array to a string property – Panagiotis Kanavos Feb 07 '17 at 13:26
Your `GetString` call (that show DQStream) isn't right, I think. Try using `UTF8` encoding – Glenn Ferrie Feb 07 '17 at 13:34
RTF isn't plain text. It contains font sizes, names, etc like `{\rtf1\ansi{\fonttbl\f0\fswiss Helvetica;}\f0\pard`. It seems the OP is confused both about BLOBs and RTF. – Panagiotis Kanavos Feb 07 '17 at 13:41

Panagiotis Kanavos · Answer 2 · 2017-02-07T13:44:31.067

BLOB means "Binary Large Object" - it is a byte array. You can't assign it directly to a string or append it. Did you mean to use a CLOB perhaps?

Furthermore, RTF isn't serialized data or raw text. It contains font names, codes etc, which means you can't just prepend text to it and get a valid RTF file. An RTF may contain embedded OLE Objects, but that is very rare these days.

For example, the following snippet is a valid RTF document. Appending this to the Text property will display all the special characters. It's not Unicode either, it's plain old ANSI:

{\rtf1\ansi{\fonttbl\f0\fswiss Helvetica;}\f0\pard
This is some {\b bold} text.\par
}

Assuming that the field type is correct, and actually contains an RTF document, you can load its contents into the RTF using the LoadFile(Stream, RichTextBoxStreamType) method. An OracleBlob is a stream, which means you can write:

var blob=dr.GetOracleBlob(1)
richTextBox1.LoadFile(blob,RichTextBoxStreamType.RichText);

Once you load the document you can manipulate it and prepend whatever you want.

Read Oracle BLOB field

2 Answers2