Devart Forums

Hi,

I need to read and write BLOBs (ca 50 - 100MB each) as fast as possible.
I don't want to allocate the whole BLOB into memory at any time, but rather use some streaming funtionality.

The Oracle LOB manual states: "The most efficient way to write large amounts of LOB data is to use OCILobWrite2() with the streaming mechanism enabled, and using polling or a callback "

What is the best way to implement this using ODAC?

Method1:
MyOraLob = MyOraTable->GetLob("MYBLOBFIELD");
MyOraLob->Write(...); //loop this statement until whole blob is written

Method2:
MyOraTableMYBLOBFIELD->LoadFromStream(...);

Method3:
MyOraStoredProc->ParamByName('blob')->AsBLOBLocator->LoadFromStream(...);
MyOraStoredProc->Execute();

Or maybe some other way that I've not yet discovered???

Are there different pro's con's of the various methods?

/Leif

The performance of all the methods that you have listed is approximately equal.
When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. In this case BLOB values will not be stored in the dataset.

When writing BLOBs to database whole BLOB value is always stored in the memory. We recommend to use the way when a BLOB value is written via parameters like your Method3. In this case only one value is held in the memory. The TOraTable component holds in the memory values for all records which BLOB fields was changed.

Okay, but isn't there any way of operating directly on a BLOB?

The Oracle manual points to either the LOBLocator API's ( OCILobWrite2() ) or the "Data Interface for persistent LOBs".

I was hoping that I could get a LOBLocator (wrapped in the TOraLob class?) and directly write to the LOB in database.

If I can't do that with ODAC, is there any way I can do that by manually calling OCI functions, or have I got it all wrong?

Use the OCILobLocator property of TOraLob to get an OCI locator. Note that the LOB locator returned by a SQL query cannot be used after the transaction is committed.

When you read BLOBs you can set CacheLobs in TOraDataSet.Options to False. Also set DeferredLobRead in TOraDataSet.Options to True. When DeferredLobRead is True the dataset do not read LOB values on opening. LOB values are fetched only when they are explicitly requested.

OK, I'll try to implement this manually using the OCILobLocator.

But, would you consider extending the functionality of ODAC to include direct streamed writing and reading of BLOBs?
I think it would be really useful for the kind of applications that must handle really large BLOBs.

Another way might be to implement the "BLOB Append" function.

/Leif

Hi,again...

I just came to think of another idea,

what about writing to a Temporary BLOB,
and then somehow copy that blob into a persistant (maybe by executing some PL/SQL code)

Can this improve things regarding memory usage?

/Leif

In the nearest future we do not plan to add the direct streamed writing of BLOBs in ODAC.

Writing in a temporary BLOB is the same as writing in a persistent BLOB. This does not have an influence on memory usage.