ORA-01480: Trailing null missing from STR bind value

[Trond]

D2006, XP SP2, DOA 4.1

I'm reading a value from a varchar2(4000) field in my database using a TOracleQuery. I've got at second TOracleQuery where I use the AsString() from the previous query directly in a bind variable parameter updating the very same record in the database. (see code) And for some records when executing the update I get the ORA-01480 message. I've tested the same records using C# and it worked fine. So I'm wondering why it's not working with DOA.

ThisQuery := TOracleQuery.Create(nil);
try
ThisQuery.Session := OracleSession;
strCommand := Format('select %s, %s from %s',[sIDColumn,sValueColumn,sTable]);
ThisQuery.SQL.Add(strCommand);
ThisQuery.Execute;
ThisUpdateQuery := TOracleQuery.Create(nil);
try
ThisUpdateQuery.Session := OracleSession;
strCommand := Format('update %s set %s=:value where %s = :id',[sTable, sValueColumn, sIDColumn]);
ThisUpdateQuery.SQL.Add(strCommand); ThisUpdateQuery.DeclareVariable('value',otString); ThisUpdateQuery.DeclareVariable('id',otInteger);
while not ThisQuery.Eof and not FStop do
begin
ThisUpdateQuery.SetVariable(0, ThisQuery.FieldAsString(1));
ThisUpdateQuery.SetVariable(1, ThisQuery.FieldAsInteger(0));
ThisUpdateQuery.Execute;
if OracleSession.InTransaction then
OracleSession.Commit;
ThisQuery.Next;
end;
finally
ThisUpdateQuery.Free;
end;
finally
ThisQuery.Free;
end;

 

[Andrey Maximenko. Ukraine]

1. I don't now :-(.
2. if OracleSession.InTransaction then OracleSession.Commit; => too many commit's => move commit after "begin.. end";
3. Use Oracle code:

UPDATE table1 tbl1
   SET svaluecolumn = (SELECT VALUE
                         FROM table2 tbl2
                        WHERE tbl1.sidcolumn = tbl2.sidcolumn)