TOracleDirectPathLoader ORA-01722 Invalid Number

[Matt Ors]

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.

Spoiler

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.

Spoiler

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:

Spoiler

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

 

[Marco Kalter]

Can you let me know the table definition?

 

[Matt Ors]

Sure! Here is the table definition:

-- Create table
create global temporary table TMP_DATAMIGRATION
(
  id                  INTEGER,
  nazwa               VARCHAR2(50),
  kraj                VARCHAR2(50),
  id_wojewodztwo      INTEGER,
  powiat              VARCHAR2(50),
  gmina               VARCHAR2(50),
  id_uke_city         INTEGER,
  id_uke_municipality INTEGER,
  id_uke_county       INTEGER,
  id_gmina            INTEGER,
  b_powiat            NUMBER(1)
)
on commit preserve rows;

I just checked that the problem still exists regardless of using polish characters in the values of the variables.

Perhaps the database is not compliant with direct loading for some reason. After all I can load this data to another database.

 

[Matt Ors]

I just created a similar table which had only varchar2 fields and loaded the data successfully! I found out that all data was shifted right by two columns!

I don't know why does it work on another database but from now on I have something I can work with.

I think this problem is solved now Sorry for bothering you Marco.

EDIT:
I loaded column indexes for the table in wrong order!

Before:

SELECT   COLUMN_NAME
      ,   DATA_TYPE
   FROM   USER_TAB_COLS
  WHERE   TABLE_NAME = :OBJECT_NAME

After:

SELECT   COLUMN_NAME
        ,   DATA_TYPE
     FROM   USER_TAB_COLS
    WHERE   TABLE_NAME = :OBJECT_NAME
 ORDER BY   COLUMN_ID