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
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.