Truncating of CLOB fields to 4000 bytes in OraQuery

[tr]

hi!

i'm using the oraquery with a table including clob fields. the fields are:

KOBOLDID NUMBER NOT NULL
TEXTMEMO CLOB

now i'm using the following pascal code to update the clob field:

// var s : string

OraQuery.SQL.Text :=
'UPDATE kobold SET TEXTMEMO=:TEXTMEMO' +
' WHERE KOBOLDID=:KOBOLDID';
OraQuery.Params.ParamByName('KOBOLDID').AsInteger := id;
OraQuery.Params.ParamByName('TEXTMEMO').Value := s;
OraQuery.ExecSQL();

it works if the string s contains data <= 4000 bytes. if s is longer the field is truncated to exactly 4000 bytes. what could be wrong?

kr,
tr

[Alex]

Please see: http://crlab.com/forums/viewtopic.php?t=95.
Also read carefully "Working with BLOB and CLOB data types" topic in ODAC help.

[tr]

thanks, i now found out that the following code seems to work:

Code: Select all

//var
//   s : string;
//   id : integer;
//   op : tOraParam;
//   ol : tOraLob;

   OraQuery.SQL.Text :=
      'UPDATE kobold SET TEXTMEMO=:TEXTMEMO' +
      ' WHERE KOBOLDID=:KOBOLDID';
   OraQuery.Params.ParamByName('KOBOLDID').AsInteger := id;
   op := OraQuery.Params.ParamByName('TEXTMEMO');
   ol := tOraLob.Create(OraSession.OCISvcCtx);
   ol.CreateTemporary(ltClob);
   ol.AsString := s;
   ol.WriteLob();
   op.ParamType := ptInput;
   op.AsOraClob := ol;  // (1)

   OraQuery.ExecSQL();  // (2)

   ol.Free();

now another question: when can i free the OraLob object? after assignment (1) or after execution (2) ?

[Alex]

You should free the temporary lob object after execution (2).