Efficient reading blobs

[wf_paul]

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]

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();
}

[Pinturiccio]

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

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.