ORA-01704

[snowboys]

When I insert information into Clob of Oracle by TOracleQuery,There is an error:ORA-01704.
My Sql:'insert into table (id,clob_col) values(10,str)',the str is string whose length is more than 4000.
Please tell me how to treat it .
Thanks.

 

[Marco Kalter]

You cannot directly insert long strings into a CLOB column. You need to write an empty_clob() first, return the CLOB field, and write the CLOB data through a TLOBLocator. The statement needs to be something like this:

insert into table (id,clob_col)
values(10,empt_clob())
returning clob_col into :clob_var

The :clob_var variable needs to be of type otCLOB. The following example is copied from the User's Guide:

var LOB: TLOBLocator;
    Buffer: array[0..99] of Byte;
begin
  // insert into lobtable (id, lobcolumn) values (:id, empty_blob())
  //   returning lobcolumn into :lobcolumn
  with LOBQuery do
  begin
    SetVariable('id', 1);
    // Create a new BLOB (initially Null)
    LOB := TLOBLocator.Create(Session, otBLOB);
    // Assign it to the returning variable
    SetComplexVariable('lobcolumn', LOB);
    Execute;
    // After the insert, use the LOB Locator to write the data
    LOB.Write(Buffer, 100);
    LOB.Free;
  end;
end;

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