Reading Oracle BLOB columns using OLE-DB

Reading BLOB columns from a Database using OLE-DB is pretty simple, there’s an MSDN sample which shows you how to do it.

However, for Oracle BLOB columns, the Oracle OLE-DB provider doesn’t bind the length part of the output column binding in the same way that the SQL-Server OLE-DB provider does.

Oracle sets the length part to 4 bytes, indicating the length of the bind value itself, which is an ISequentialStream* pointer, rather than the size of the BLOB column contents itself.

The SQL-Server OLE-DB provider, instead, sets the length part to the size of the Image column contents. Which is convenient in this case as you can make a single call to the ISequentialStream::Read() method using the length part as the 2nd parameter.

To read the Oracle BLOB column contents, you have to make repeated calls to the ISequentialStream::Read() method using some arbitrary value as the 2nd parameter until the stream is completely read, which invariably involves multiple memory reallocations, as shown below:

  ISequentialStream* spStreamToCopyTo = reinterpret_cast<ISequentialStream*>(spSource);
  HRESULT hr = S_OK;

  ULONG ulBytesRead = 0;
  int cbBuf = 0x400000;
  BYTE* pBuf = new BYTE[cbBuf];
    if(SUCCEEDED(hr = spStreamToCopy->Read(pBuf, cbBuf, &ulBytesRead)))
       hr = spStreamToCopyTo->Write(pBuf, ulBytesRead, NULL);
  while((cbBuf == ulBytesRead) && SUCCEEDED(hr));
  delete[] pBuf;

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

2 thoughts on “Reading Oracle BLOB columns using OLE-DB

  1. I’m looking for a good work-around. I have queries that return a lot of blobs between 1.000kb and 10.000kb. It takes up to fifteen times longer than the same query with SQL-Server Driver & Database…

    Would instead help using the LONG RAW datatype??


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.