Where is streamed blob data saved until commit?

Question

From the devart docs it says that:

Note that writing data to OracleLob object results in immediate data transfer to server.

But it doesn't mention how this actually works. For instance, in the following example (from the docs):

public void UploadBlob(OracleConnection myConnection) 
{ 
  FileStream fs = new FileStream("D:\\Tmp\\_Water.bmp", FileMode.Open, FileAccess.Read); 
  BinaryReader r = new BinaryReader(fs); 
  myConnection.Open(); 
  OracleLob myLob = new OracleLob(myConnection,OracleDbType.Blob); 
  int streamLength = (int)fs.Length; 
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength); 
  OracleCommand myCommand = new OracleCommand("INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'Water',:Pictures)", myConnection); 
  OracleParameter myParam = myCommand.Parameters.Add("Pictures", OracleDbType.Blob); 
  myParam.OracleValue = myLob; 
  try 
  { 
    Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected."); 
  } 
  finally 
  { 
    myConnection.Close(); 
    r.Close(); 
    fs.Close(); 
  } 
}

... then according to the docs, data should be sent to the database server on line myLob.Write(r.ReadBytes(streamLength), 0, streamLength); - but at this point the database does not know in which "record" the blob is to be stored, so I am a bit in doubt on how this actually works - where is the data written to? Is there some kind of in-memory staging area where the data is streamed to, and depending on if I commit or rollback, the data is made available in the specific record (or removed if rolled back)?

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

From devart docs

LOB is considered temporary if it has no references from tables on the server. Temporary LOBs are used to insert new data into tables.

Note that temporary LOBs are destroyed when a transaction is committed.

And then, from oracle docs

Managing Temporary LOBs

The database keeps track of temporary LOBs in each session, and provides a v$ view called v$temporary_lobs. From the session, the application can determine which user owns the temporary LOB. As a database administrator, you can use this view to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

Managing Temporary Tablespace for Temporary LOBs

Temporary tablespace is used to store temporary LOB data. As a database administrator you control data storage resources for temporary LOB data by controlling user access to temporary tablespaces and by the creation of different temporary tablespaces.

So, when you are inserting, you write to a temporary tablespace. When you commit, Oracle copies that data to "its place" (typically a LOB segment in some tablespace, but for short LOBs it might just store it inline with the other row data).

Thanks for sharing this. I'm working with streaming functionality from ASP.NET WebApi to Oracle at the moment and besides getting things to work, I would like to understand how things *actually* work under the hood in the various APIs. This helped me a lot - thanks again. — Lasse Christiansen, Jul 15 '14 at 23:17

Where is streamed blob data saved until commit?

1 Answers1