Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Not able to get Stream from Blob

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.

This post has been answered by Ruslan Nigmatullin on Feb 21 2025
Jump to Answer

Comments

Post Details

Added on Jan 27 2025
9 comments
154 views