ORA-01459 when editing a TSmartQuery with memo field

[cis-wurzen]

I've noticed a ORA-01459 problem when editing a TSmartQuery with a memo field after switching to Unicode DB access.

The test case for this is the following:

SQL for the test table

Code: Select all

CREATE TABLE ODACTESTUNICODEMEMOUPDATETAB(
  ID                                 NUMBER(10,0) NOT NULL,
  FIELD1                             VARCHAR2(400)
);

ALTER TABLE ODACTESTUNICODEMEMOUPDATETAB ADD CONSTRAINT ODACTESTUNICODEMEMOUPDATETABPK PRIMARY KEY (ID);

INSERT INTO ODACTESTUNICODEMEMOUPDATETAB(ID) VALUES(1);

COMMIT;

Delphi console application

Code: Select all

program ODACUnicodeMemoUpd;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DBAccess,
  Ora,
  OraCall,
  OraSmart;

{
CREATE TABLE ODACTESTUNICODEMEMOUPDATETAB(
  ID                                 NUMBER(10,0) NOT NULL,
  FIELD1                             VARCHAR2(400)
);

ALTER TABLE ODACTESTUNICODEMEMOUPDATETAB ADD CONSTRAINT ODACTESTUNICODEMEMOUPDATETABPK PRIMARY KEY (ID);

INSERT INTO ODACTESTUNICODEMEMOUPDATETAB(ID) VALUES(1);

COMMIT;
}

const
  cServer = 'YourServer';
  cUsername = 'YourUser';
  cPassword = 'YourPassword';

function TestUnicodeMemoUpdate(ATest: Integer): Boolean;
var
  se: TOraSession;
  qr: TSmartQuery;
  NewID: Integer;
begin
  se := TOraSession.Create(nil);
  qr := TSmartQuery.Create(nil);
  try
    se.Server := cServer;
    se.Username := cUserName;
    se.Password := cPassword;
    se.Options.UseUnicode := True;
    with qr do
    begin
      Session := se;
      SQL.Add('SELECT * FROM ODACTESTUNICODEMEMOUPDATETAB');
      Keyfields := 'ID';

      Options.LongStrings := False;

      SQLUpdate.Add('UPDATE ODACTESTUNICODEMEMOUPDATETAB SET');
      SQLUpdate.Add('  ID     = :ID,');
      SQLUpdate.Add('  FIELD1 = :FIELD1');
      SQLUpdate.Add('WHERE');
      SQLUpdate.Add('      (ID = :OLD_ID)');
      UpdatingTable := 'ODACTESTUNICODEMEMOUPDATETAB';

      Open;
      Edit;
      if ATest = 1 then
      begin
        NewID := FieldByName('ID').AsInteger + 1;
        FieldByName('ID').AsInteger := NewID;
      end
      else
      if ATest = 2 then
        FieldByName('FIELD1').AsString := 'Foo';
      Post;
      Close;
      Open;
      if ATest = 1 then
        Result := FieldByName('ID').AsInteger = NewID
      else
      if ATest = 2 then
        Result := FieldByName('FIELD1').AsString = 'Foo';
      Close;
    end;
  finally
    se.Free;
  end;
end;

begin
  try
    if TestUnicodeMemoUpdate(1) then
      WriteLn('PASS')
    else
      WriteLn('FAIL');
  except
    on E: Exception do
    begin
      WriteLn('FAIL - Exception Error');
      WriteLn('  E.ClassName = ', E.ClassName);
      WriteLn('    E.Message = ', E.Message);
    end;
  end;
  ReadLn;
end.

Steps:
- execute the script
- create a new console application in Delphi and paste the code
- adjust the constants cServer, cUserName and cPassword
- compile and run the example

expected: output is PASS
actual:
Delphi XE U#1 with ODAC 7.20.0.7 or 8.0.2 and Delphi XE2 U#1 Win32 with ODAC 8.0.2:
output is
FAIL - Exception Error
E.ClassName = EOraError
E.Message = ORA-01459: invalid length for variable character string


It passes when TOraSession.Options.UseUnicode is False, but that's of course no workaround. Furthermore when I call TestUnicodeMemoUpdate with ATest = 2 then I do see the error "ORA-01480: trailing null missing from STR bind value tips".

[AlexP]

Hello,

Thank you for the information.
We have reproduced the problem.
We will try to fix it in the nearest product version.