Setting TOracleQuery's clob variable before exec?

[soli]

Hi All,
mu question is: I need to use in my TOracleQuery (update) a variable of kind otClob. I can't figure out, how I can set its value. I have a TMemoryStream with the data I should somehow insert into the destination table.
I'm sure that this is possible, but how?
Thanks in advance,
Soli

 

[Marco Kalter]

You can't set the CLOB data in an update statement. You need to obtain a LOB Locator for this CLOB column and lock the record, and use the LOB Locator to write the data. A LOB Locator is encapsulated in the TLOBLocator class, which is a TStream descendant. Writing the contents of a TMemoryStream is therefore very straightforward.

var LOB: TLOBLocator;
begin
  // update lobtable
  //   set lobcolumn = empty_clob()
  //   where id = :id
  //   returning lobcolumn into :lobvar
  with LOBQuery do
  begin
    SetVariable('id', 1);
    // Create a new BLOB (initially Null)
    LOB := TLOBLocator.Create(Session, otCLOB);
    // Assign it to the returning variable
    SetComplexVariable('lobvar', LOB);
    Execute;
    // After the update, use the LOB Locator to write the data
    LOB.CopyFrom(MyMemoryStream, 0);
    LOB.Free;
    // Commit the update
    Session.Commit;
  end;
end;

This example first updates the CLOB column to make it empty, which also locks the record and returns an initialized LOB Locator. Next the new data is written and committed.

------------------
Marco Kalter
Allround Automations