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.
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.
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:
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
Sorry for bothering you Marco.