sqlldr: Record <>: Rejected - Error on table <>, column <>. on Linux server.

Hi All

This I find this issue when tried to load the CSV file with SQL Loader after migration from Unix to Linux.
I put the file using samba from Windows to Linux directory.

When I run: 
sqlldr apps/apps_pwd  MY_CTL_FILE.ctl MY_CTL_FILE.log

with CTL file:

LOAD DATA
INFILE 'pa_transaction_interface_all.csv'
BADFILE 'pa_transaction_interface_all.bad'
DISCARDFILE 'pa_transaction_interface_all.dsc'
APPEND INTO TABLE PA.PA_TRANSACTION_INTERFACE_ALL
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(TRANSACTION_SOURCE CONSTANT"Bateman IL Time Sheets",
 TRANSACTION_STATUS_CODE CONSTANT"P",
 BATCH_NAME,
 EXPENDITURE_ENDING_DATE,
 EMPLOYEE_NUMBER,
 EXPENDITURE_ITEM_DATE,
 PROJECT_NUMBER,
 TASK_NUMBER,
 EXPENDITURE_TYPE,
 QUANTITY,
 ORIG_TRANSACTION_REFERENCE,
 ORGANIZATION_NAME,
 ORG_ID,
 UNMATCHED_NEGATIVE_TXN_FLAG "CASE WHEN :QUANTITY < 0 THEN 'Y' ELSE NULL END")

Where ORG_ID and QUANTITY are NUMBER.


I got the following error:

SQL*Loader: Release 8.0.6.3.0 - Production on Thu May 5 18:07:42 2011

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Control File:   /interfaces/itime/pa_transaction_interface_all.ctl
Data File:      pa_transaction_interface_all.csv
  Bad File:     /interfaces/itime/pa_transaction_interface_all.dat
  Discard File: pa_transaction_interface_all.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     1 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table PA.PA_TRANSACTION_INTERFACE_ALL, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BATCH_NAME                          FIRST     *   ,  O(") CHARACTER           
EXPENDITURE_ENDING_DATE              NEXT     *   ,  O(") CHARACTER           
EMPLOYEE_NUMBER                      NEXT     *   ,  O(") CHARACTER           
EXPENDITURE_ITEM_DATE                NEXT     *   ,  O(") CHARACTER           
PROJECT_NUMBER                       NEXT     *   ,  O(") CHARACTER           
TASK_NUMBER                          NEXT     *   ,  O(") CHARACTER           
EXPENDITURE_TYPE                     NEXT     *   ,  O(") CHARACTER           
QUANTITY                             NEXT     *   ,  O(") CHARACTER           
ORIG_TRANSACTION_REFERENCE           NEXT     *   ,  O(") CHARACTER           
ORGANIZATION_NAME                    NEXT     *   ,  O(") CHARACTER           
ORG_ID                               NEXT     *   ,  O(") CHARACTER           
UNMATCHED_NEGATIVE_TXN_FLAG          NEXT     *   ,  O(") CHARACTER           

TRANSACTION_SOURCE             CONSTANT 'Bateman IL Time Sheets'
TRANSACTION_STATUS_CODE        CONSTANT 'P'

Column UNMATCHED_NEGATIVE_TXN_FLAG had SQL string
"CASE WHEN :QUANTITY < 0 THEN 'Y' ELSE NULL END"
applied to it.

Record 1: Rejected - Error on table PA.PA_TRANSACTION_INTERFACE_ALL, column ORG_ID.
ORA-01722: invalid number

Record 2: Rejected - Error on table PA.PA_TRANSACTION_INTERFACE_ALL, column ORG_ID.
ORA-01722: invalid number

Record 3: Rejected - Error on table PA.PA_TRANSACTION_INTERFACE_ALL, column ORG_ID.
ORA-01722: invalid number
..............................................................................................................................................................
Record 51: Rejected - Error on table PA.PA_TRANSACTION_INTERFACE_ALL, column ORG_ID.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table PA.PA_TRANSACTION_INTERFACE_ALL:
  0 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                   3109 bytes(1 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:            51
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Thu May 05 18:07:42 2011
Run ended on Thu May 05 18:07:42 2011

Elapsed time was:     00:00:00.07
CPU time was:         00:00:00.00   
Total logical records skipped:          0
Total logical records read:            51
Total logical records rejected:        51
Total logical records discarded:        0

I checked the file and it's still working perfect in UNIX env.
I also checked the file with "file" command in both servers and WOOW, the results are different.


In Unix:
file pa_transaction_interface_all.csv

pa_transaction_interface_all.csv: ASCII text

In Linux:
file pa_transaction_interface_all.csv

pa_transaction_interface_all.csv: ASCII text, with CRLF line terminators

I'm really don't know why.

So I just did the following:

dos2unix pa_transaction_interface_all.csv   
and SQL Loader loaded all rows.

Cool.. Good luck..