Hello

My IDE is Embarcadero Delphi 10.2 Version 25.0.27659.1188 with 10.2 Update 1 installed.
The version of Direct Oracle Access is 4.1.3.5 (object version).

I try to load the same record into two databases.
Loader fails to load the record to one database only - in case of the other, it works successfully without any errors!

Furthermore, the ORA-01722 happens when I'm trying to load AnsiString value to VARCHAR2 field! Why would Oracle even try converting it to a number?

I prepared a simple code to demonstrate my issue. When the error happens the value of LastColumn is 3.
I have also tried using AnsiString and AnsiString(1250) types but with no effect. I am using RawByteString in the actual code because I wish to avoid RTL code page conversions of the source data.

Code
procedure TdmMain.DirectPathLoad;
var
  Loader: TOracleDirectPathLoader;
  Row: Integer;
  tmpString02: RawByteString;
  tmpString03: RawByteString;
  tmpString04: RawByteString;
  tmpString05: RawByteString;
  tmpString06: RawByteString;
  tmpString07: RawByteString;
  tmpString08: RawByteString;
  tmpString09: RawByteString;
  tmpString10: RawByteString;
begin
  Loader := TOracleDirectPathLoader.Create(nil);
  try
    Loader.Session := Self.oracleSession;
    Loader.TableName := 'TMP_DATAMIGRATION';
    Loader.GetDefaultColumns(True);
    Loader.Prepare;

    //SEE THE EDIT AT THE END OF THE POST FOR THE CORRECT VALUES
    tmpString02 := '50';
    tmpString03 := 'Sokolka'; 
    tmpString04 := 'POLSKA';
    tmpString05 := '1';
    tmpString06 := 'sokolski';
    tmpString07 := 'Sokolka';
    tmpString08 := '0923443';
    tmpString09 := '2011';
    tmpString10 := '2011084';

    Loader.Columns[2].SetData(0, @tmpString02[1], Length(tmpString02));
    Loader.Columns[3].SetData(0, @tmpString03[1], Length(tmpString03));
    Loader.Columns[4].SetData(0, @tmpString04[1], Length(tmpString04));
    Loader.Columns[5].SetData(0, @tmpString05[1], Length(tmpString05));
    Loader.Columns[6].SetData(0, @tmpString06[1], Length(tmpString06));
    Loader.Columns[7].SetData(0, @tmpString07[1], Length(tmpString07));
    Loader.Columns[8].SetData(0, @tmpString08[1], Length(tmpString08));
    Loader.Columns[9].SetData(0, @tmpString09[1], Length(tmpString09));
    Loader.Columns[10].SetData(0, @tmpString10[1], Length(tmpString10));
    try
      Loader.Load(1);
    except
        on E: EOracleError do
        begin
          ShowMessage(E.Message + #13#10 + 'Row = ' + IntToStr(Loader.LastRow) + #13#10 + 'Column = ' + IntToStr(Loader.LastColumn));
          Loader.Abort;
          raise;
        end;
    end;
    Loader.Finish;
  finally
    FreeAndNil(Loader);
  end;
end;

The oracleSession has the following non-default settings.
On the side node, I cannot change the Preferences.ConvertUTF setting to any other value.
Code
  object oracleSession: TOracleSession
    ThreadSafe = True
    Preferences.ConvertUTF = cuUTF8ToUTF16
    RollbackOnDisconnect = True
    Left = 24
    Top = 54
  end

I compared V$PARAMETER views between databases using this query:
SQL Query
  SELECT   NUM
       ,   NAME
       ,   TYPE
       ,   VALUE
       ,   DISPLAY_VALUE
       ,   ISSES_MODIFIABLE
       ,   ISSYS_MODIFIABLE
       ,   ISINSTANCE_MODIFIABLE
    FROM   V$PARAMETER
ORDER BY   NAME
The only differences I found are in these parameters:
  • db_file_multiblock_read_count
  • log_buffer
  • memory_max_target
  • memory_target
  • sga_max_size
  • shared_pool_reserved_size
I have also compared the value of NLS_LANG environment variable between these two databases.
The database which the issue affects did not have a value set for that variable.
I have set NLS_LANG in ~/.bashrc to the same value as in the compatible database but unfortunately the issue still persists.

What else do you suggest to check or do? I really have no idea what is the reason behind the ORA-01722 error. I think it is database-related.

EDIT:
Unfortunately, the post cannot contain polish characters. I copy-pasted the initialization of AnsiString variables to here: https://pastebin.com/hS7LVeLF

Last edited by Matt Ors; 02/22/18 01:34 PM. Reason: Polish characters broke the post