LOB network latency

[JEM]

I use a TOracleQuery to load some data with a blob field. It takes about 10 seconds to load 700 rows. Average blob size is around 3k, but some are as large as 64k. Oracle Monitor indicates most of the time spent is network latency due to many LOB.Length and LOB.Read() operations. Is there a way I can read all 700 records/blobs in one go without this network traffic going back and forth?

I tested one theory using utl_raw.cast_to_varchar2, this sort-of works, but requires workarounds for the large blobs, was hoping there would be a more straight forward solution. I need data for read-only.

 

[Marco Kalter]

That is indeed correct, there are some network roundtrips required to fetch the BLOB data. I am not aware of any workarounds.

 

[JEM]

Thanks for the quick response, wish there was a better solution, but here is what I ended up with (which solves my problem for the time being as only 10% of records are larger than 4k)

create or replace function blob_to_varchar2(b blob)
return varchar2 is
begin
if (length(b) between 1 and 4000) then
return utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b),1));
else
return null;
end if;
end;
/

and Delphi code something along the lines:

qry.SQL.Add('select my_blob, blob_to_varchar2(my_blob) from my_table');
qry.Execute;
.....
if qry.FieldIsNull(1) then
Result := qry.FieldAsString(0)
else
Result := qry.FieldAsString(1);