Trying to read blob

Question

I am trying to read a blob from an Oracle database. The GetFileContent function takes p_file_id as a parameter and returns a BLOB. BLOB is a DOCX file that needs to be written somewhere in a folder. But I can not understand how to read BLOB. Something is definitely stored in the return_value-paramater parameter after

OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); 

The value is {byte [9946]}. But I get an error on execution

 long retrievedBytes = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize); 

It states that an InvalidOperationException was detected: "There is no data for a 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 a blob from a function?

Answer 1

It looks like you are using Microsoft Oracle Client. You probably want to use 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 :: Reading Method

class OracleLob

OracleDataReader :: GetOracleLob Method

On the other hand, the Microsoft Oracle client is depreciating. You might want to upgrade to Oracle ODP.net, as this will be the only official support client to move forward.