Efficient reading blobs

Efficient reading blobs

Postby wf_paul » Thu 20 Mar 2014 00:25

Do you have an example of a memory friendly way to read a Blob value from Oracle? The examples in the documentation get the full value of the blob in a byte[] . When I try to read from the OracleLob stream in smaller chunks it still ends up loading the full byte[] into the .Value property. I am trying to write the blob to a file stream.

Thanks,
Paul
wf_paul
 
Posts: 7
Joined: Wed 23 May 2012 18:48

Re: Efficient reading blobs

Postby wf_paul » Thu 20 Mar 2014 10:51

Here is a simple example to re-produce the issue.

Code: Select all
using (FileStream streamToSaveLobTo = new FileStream(@"C:\temp\someFile.bmp", FileMode.OpenOrCreate, FileAccess.Write))
{
   using (OracleConnection myConn = new OracleConnection("CONNECTION STRING HERE"))
   {
      int BUFF_SIZE = 10000;
      myConn.Open();
      using (OracleCommand myCommand = new OracleCommand("Select Blob_column from TableWithBlob and rownum = 1" , myConn))
      {
         myCommand.Cached = false;
         myCommand.CommandType = System.Data.CommandType.Text;

         OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
         OracleLob oracleBlob = null;
         if (myReader.Read())
         {
            oracleBlob = myReader.GetOracleLob(0);
            oracleBlob.Cached = false;

            int bytesRead = 0;
            byte[] chunk = new byte[BUFF_SIZE];
            //each interation memory size increases by buffer size
            while ((bytesRead = oracleBlob.Read(chunk, 0, BUFF_SIZE)) > 0)
            {
               streamToSaveLobTo.Write(chunk, 0, bytesRead);
               streamToSaveLobTo.Flush();
            }
            
         }
         myConn.Close();
      }
   }
   streamToSaveLobTo.Flush();
   streamToSaveLobTo.Close();
}
wf_paul
 
Posts: 7
Joined: Wed 23 May 2012 18:48

Re: Efficient reading blobs

Postby Pinturiccio » Fri 21 Mar 2014 16:21

When you access the Value property of an OracleLob object, the whole LOB is read from the database to the RAM buffer.

wf_paul wrote:Here is a simple example to re-produce the issue.

We could not reproduce the issue. We have run the application twice. First it read a 40 Kb LOB, then a 100 Mb LOB. In both cases Task Manager displayed the same amount of RAM used.

Note that RAM usage must be tested not in the Debug mode, but without debugging. Use Start Without Debbuging (Ctrl+F5) to start the application.
Pinturiccio
Devart Team
 
Posts: 1885
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle