CLOB fields not generically assignable

Tom R · Post by **Tom R** » Wed 02 Nov 2005 15:26

The scenario:
Delphi 7
NET connection
non-persistent fields (dynamic queries)
dbexpoda 2.50.6.0

When attempting to assign values (targetDS.FieldByName(fn).Value := sourceDS.FieldByName(fn).Value), I get the exception:

ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 819
ORA-06512: at line 1

This code worked fine with dbexpoda 1.87, but we need to upgrade for Win2003 SP1 and Oracle 10gR2. Using an OCI connection also works, but we support so many DB servers that managing tnsnames.ora would be a nightmare.

I'm looking for either a workaround (other than determining whether the field is a CLOB or not before setting its value) or a fix.

-Tom R

Paul · Post by **Paul** » Thu 03 Nov 2005 08:20

We cannot reproduce your problem with Oracle server 9.2.0.1, Oracle server 10.2.0.1, database charset AL32UTF8, DbxOda 100. We tried to insert 15000 of English characters.
Please specify your version of Oracle server, database charset, client charset (in TSQLConnection parameters)

Tom Reedy · Post by **Tom Reedy** » Thu 03 Nov 2005 19:15

targetDS is connected to Oracle server 10.1.0.2, charset WE8MSWIN1252 and sourceDS is connected to Interbase 6.0 with dbexpint 7.0.2.113.

I found a work-around by using an intermediate TStringList to transfer the content:
slClobValue := TStringList.Create;
slClobValue.Text := sourceDS.fieldbyname(fn).AsString;
TargetDS.fieldbyname(fn).assign(slClobValue);

The same error occurs when directly saving the content of a TRichEdit to a CLOB TField:
targetDS.fieldbyname(fieldName).assign(memNote.lines);

The workaround for this is to stream to a TStringList first:
TempStringList := TStringList.Create;
memNote.Lines.SaveToStream(TempStream);
TempStream.Position := 0;
TempStringList.LoadFromStream(TempStream);
fieldbyname(fn).assign(TempStringList);

The workarounds get me by for now, but are inefficient.

Tom Reedy