I am trying to read a BLOB from an Oracle database. The function GetFileContent take p_file_id as a paramater and return a BLOB. The BLOB is a DOCX-file that needs to be written in a folder somewhere. But I can't quite figure out how to read the BLOB. There is definitely something stored in the return_value-paramater after
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
The value is {byte[9946]}. But I get an error when executing
long retrievedBytes = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize);
It says InvalidOperationException was caught: "No data exists for the row or column."
Here is the code:
cmd = new OracleCommand("GetFileContent", oraCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_file_id", OracleType.Number).Direction = ParameterDirection.Input;
cmd.Parameters[0].Value = fileID;
cmd.Parameters.Add("return_value", OracleType.Blob).Direction = ParameterDirection.ReturnValue;
cmd.Connection.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
MemoryStream memory = new MemoryStream();
long startIndex = 0;
const int ChunkSize = 256;
while (true)
{
byte[] buffer = new byte[ChunkSize];
long retrievedBytes = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize); //FAILS
memory.Write(buffer, 0, (int)retrievedBytes);
startIndex += retrievedBytes;
if (retrievedBytes != ChunkSize)
break;
}
cmd.Connection.Close();
byte[] data = memory.ToArray();
memory.Dispose();
How can I read the BLOB from the function?
Looks like you are using the Microsoft Oracle Client. You probably want to use the LOB objects rather than using GetBytes(...).
I think the first link below would be the easiest for you. Here is an excerpt:
using(reader)
{
//Obtain the first row of data.
reader.Read();
//Obtain the LOBs (all 3 varieties).
OracleLob BLOB = reader.GetOracleLob(1);
...
//Example - Reading binary data (in chunks).
byte[] buffer = new byte[100];
while((actual = BLOB.Read(buffer, 0, buffer.Length)) >0)
Console.WriteLine(BLOB.LobType + ".Read(" + buffer + ", " + buffer.Length + ") => " + actual);
...
}
OracleLob::Read Method
OracleLob Class
OracleDataReader::GetOracleLob Method
On a side note, the Microsoft Oracle client is being depreciated. You may want look into switching to Oracle's ODP.net, as that will be the only "Officially Supported" client moving forward.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With