SQL Loader failing while loading a .txt file which is a result of compare-object cmdlet

Question

I receive a fixed format file out of a legacy application that is loaded to an oracle table by SQL loader daily. A new requirement needs Day 1 and Day 2 files to be compared and only the difference to be appended, so the data at the end of day 2 looks like Day 1(initial load) + Day 2 - Day 1(changes only).

I'm using below command to find out the difference between 2 files

Compare-Object -referenceObject $(Get-Content $File1) -differenceObject $(Get-Content $File2) | %{$.Inputobject + $.SideIndicator} | ft -auto | out-file $fileDiff.txt -width 5000

A sample first line from the output of compare-object looks like below:

4614 TESTUC1 32570544900721657

The control file reads the infile as below:

PER_ID POSITION(1:8) CHAR TERMINATED BY WHITESPACE,

USER_ID POSITION(9:16) CHAR TERMINATED BY WHITESPACE,

USER_ID_PREV POSITION(17:24) CHAR TERMINATED BY WHITESPACE,

Logs give me an error reason for discarding all records as follows:

Record 1: Rejected - Error on table PDB_EXPORT_DELTA, column PER_ID. ORA-01722: invalid number

I'm not sure if it is due to the FileDiff.txt not being in fixed format anymore or SQLLoader not recognizing it as a fixed format anymore.

I've researched both aspects and haven't found anything on Compare-Object preserving or altering the format of the output difference file, nor have seen anything on type-casting values in SQLLoader control file. Any help is much appreciated. Thoughts on other ways of achieving the before said requirement is also welcome. TIA

Answer 1

My version of SQLLoader (and apparently yours) defaults to an 8-Bit characterset so you can either A) convert the file to 8-Bit using Notepad++ (or equivalent) or B) update your powershell script to create the file as 8-Bit or C) review use of the CHARACTERSET clause in your SQLLoader control file.