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 += "";
        }

Crankle

I think you need to do this first: OracleBlob blob = (OracleBlob)dr.GetOracleBlob(1); – Mulciber

See after de dr.read. i am doing that already. – Crankle

You don't seem to be using Blob1. I just posted some sample code. – Mulciber

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. – Olly

Did my answer help? – Mulciber

I came back to this question via Google. I ran into the same issue myself. – Mulciber

Answer 1

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));

Mulciber

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 – Olly

Your GetString call (that show DQStream) isn't right, I think. Try using UTF8 encoding – Mulciber

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. – Olly

Answer 2

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.

Olly

Answer 3

I'm not sure how you want to display the byte[] array in the textbox, that depends on what the blob represents or if you just want to display comma delimited byte numbers. To convert an OracleBlob to Byte[] you can do this:

object val = theblob;  // val is the OracleBlob, however you get it.
object result;         // this stores the byte[] for later translation to your textbox.

int size = Convert.ToInt32(((OracleBlob)val).Length);
result = new byte[size];
((OracleBlob)val).Read((byte[])result, 0, size); 

Dobruja