Read Oracle BLOB data as chunks.

Question

Hi, I have the following queries on fetching a BLOB data from Oracle ( I am trying to use OracleDataReader - .Net to read the BLOB value.):

Is it possible to read a BLOB data on Oracle database as chunks without loading the entire BLOB on to server memory? I believe OracleDataReader.GetBytes() will load the entire blob on server memory.

Passing a null buffer on to GetBytes() fetches the size of the BLOB but would that require the BLOB to be loaded on server's memory?

What would be the optimal way to fetch the BLOB size and BLOB data as chunks without loading the entire BLOB in memory?

Can someone please help?

Answer 1

Look at DBMS_LOB.READ

Answer 2

Thanks Gary.

But will that be an overhead for the DB because say if I am going to set the buffer size as 32 kb for a file with 10 MB size? I might have to execute the stored proc from my code multiple times so as to stream the complete file. What if 1000 clients hit the server and all of them try downloading such huge files?

From few of the articles read on web, I came to understand that setting the CommandBehaviour to SequentialAccess in case of SQLServer streams data with the required buffer size without loading the entire BLOB in memory. But it said that Oracle ignores this commandbehaviour.

Will it be possible to do this with OracleDataReader as in the case of SQLDataReader?