Oracle and Blobs

More fun with Oracle! I was reading an Oracle table that contained a "LONG RAW" field. You're supposed to be able to get at those types with the GetBytes method. According to the MSDN documentation for IDataRecord.GetBytes:

If you pass a buffer that is a null reference (Nothing in Visual Basic), GetBytes returns the length of the row in bytes.

You can then use the length returned to create a buffer of the proper size and call GetBytes again, passing the buffer to do the actual read. Unfortunately, Oracle (9i) was returning zero. There was no way the data was zero bytes because I knew I was reading the proper row based on the other fields in the recordset. Googling revealed a few hits about a special Oracle datatype called OracleBinary (along with OracleClob and OracleBlob). None of these worked either.

I finally found a reference to a special property of the OracleCommand object called "InitialLONGFetchSize". By default, this is zero which means that no raw/longraw data is initially fetched when the command is executed. You're instead supposed to use the OracleBinary to fetch them but that wasn't working. If you change "InitialLONGFetchSize" to a non-zero value, this will tell Oracle how much of the raw/longraw data should be pulled back on the initial execution. There's a max of 32k (32767 bytes), so you still may need multiple reads, but you'll at least get your data.

I set this to a non-zero value and the Oracle command finally allowed me to use the GetBytes() method to retrieve the length and then the contents.