Hello,
On project we use .Net 9 and “Oracle.ManagedDataAccess.Core” v23.7.0.
We trying to get use the Stream out of Blob and return it directly to user without loading the file into memory.
If with Function we more or less found a way to do that:
await using OracleDataReader dr = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken);
await dr.ReadAsync(cancellationToken);
OracleBlob blob = dr.GetOracleBlob(0);
// Return Task<Stream>
return blob;
Though it slower than loading it into memory:
var result = await command.ExecuteScalarAsync(cancellationToken);
return result;
But, we cannot achieve correct behavior with Stored Procedure.
In our case it returns not only Blob, but also fields that explain it's type, name, etc.
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = <command>;
command.Parameters.Add(cursor);
await using OracleDataReader mainReader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken);
if (await mainReader.ReadAsync(cancellationToken))
{
var result = new OracleFileResult();
var rowData = new JsonObject();
for (var i = 0; i < mainReader.FieldCount; i++)
{
if (mainReader.GetProviderSpecificFieldType(i) == typeof(OracleBlob))
{
result.Data = mainReader.GetOracleBlob(i);
}
else
{
rowData[mainReader.GetName(i)] = GetJsonNode(mainReader[i]);
}
}
result.DataInfo = rowData;
return result;
}
Even if it returns the Stream, it make it MUCH slower, ~100 seconds against ~5 seconds with
await command.ExecuteNonQueryAsync(cancellationToken);
...
result.Data = new MemoryStream((byte[])reader[i]);
As I dig deeper, I saw that under the hood it's doing “GetBytes[]” with hundreds of requests to DB, with each request taking ~600 ms.
Did I need to add something else in the code, or made some changes in Oracle side to stream the data?
Thank you.