Insert and Update CLOB fields over 4000 characters

[dmuntane]

When inserting/updating CLOB fields with a size over 4000, field value is set to null.

Environment:
Delphi 7
dbexpoda.dll: Version 4.50.0.21
Oracle: Version 9i and 10 (XE)

Test cases:

CREATE TABLE TEST (CLOBFIELD CLOB)
INSERT INTO TEST (CLOBFIELD) VALUES ()
Result: No data is stored

UPDATE TEST SET CLOBFIELD=
Result: Data deleted (CLOBFIELD=null)


I am also using .NET driver and it works!


Regards,
dvd

[bork]

Hello

You can insert CLOB data by the following code:

Code: Select all

var
  i: integer;
  str: string;
begin
  for i := 0 to 19999 do
    str := str + Char(RandomRange(byte('a'), byte('z')));

  SQLQuery1.SQL.Text := 'insert into btest6 (id, name, myclob) values (10, ''test clob'', EMPTY_CLOB()) ' + #13 +
                        'RETURNING ' + #13 +
                        '  myclob ' + #13 +
                        'INTO ' + #13 +
                        ' :myclob';

  SQLQuery1.ParamByName('myclob').ParamType := ptInput;
  SQLQuery1.ParamByName('myclob').DataType := ftOraClob;
  SQLQuery1.ParamByName('myclob').Value := str;

  SQLQuery1.ExecSQL;
end;

The DDL script for creating this table:

Code: Select all

CREATE TABLE TEST_CLOB (
  ID NUMBER,
  NAME VARCHAR2(50),
  MYCLOB CLOB,
  CONSTRAINT PK_TEST_CLOB PRIMARY KEY (ID)
)