As someone new to OCI, I find it hard understanding how LOBs work. Can someone please explain? While I have read the manual and looked at the demos, I am still finding it very hard. Are there simple sample codes that just read to and write from multiple LOBs in the same code ?
Oracle does distinguish a LOB (data lying in some data segment) and LOB handler (something file file handle in Unix). A lob handle can created only inside the database. For example this statement:
insert into t(id, lob_col) values(1, emtpty_blob()) returning lob_col into :bval;
This will insert a row into the database, will create an empty BLOB and will return the lob handler to the application. The bind variable "bval" must be registered (bound) as OUT parameter. Here many devs do not understand "why LOB is an out parameter" when they want to "insert" some data. The key point is that lob handle can be created only on the database side. And when you have it you can use it as it was a file handle.
You just have to use OCIRead2/OCIWrire2/OCIOpen/OCIClose functions.
When you want to update a LOB, it's underlying row must be locked:
select id, lob_col from t where id = 1 for update;
This will return a LOB handle which can be opened for writing. You can write to a LOB handle as long as the transaction is open. When you commit the transaction any further writes to a lob handle will fail.
You can allocate as many lob handles as you want and you can use in any order. Just keep in mind that every lob handle is associated with one DB connection and is valid as long as this connection is alive.
Edited: One more comment: Do not use OCILobRead but use OCILobRead2. When OCILobRead Accepts an offset to be read in bytes, but returns a amount of characters read. When you read UTF8 encoded CLOBS you get a mess when you assume the #bytes=#chars. OCILobRead2 returns both #chars read and also #bytes read.
PS: Warning. A LOB starts with the 1st byte/char. You can not seek to 0th position.