ORA-01722: Invalid Number using SQL*Loader when column value equals 0
I'm receiving an ORA-01722: Invalid Number error when I'm trying load a .csv file with SQL*Loader and the "offending" column value is 0.
Below is my table_layout:
CREATE TABLE "GROSS_PAYROLL_AMT_STG"
(
"REC_ID" NUMBER,
"EMPLOYEE_NUMBER" VARCHAR2(10 BYTE),
"GROSS_PAYROLL_AMT" NUMBER,
"PAY_PERIOD_END_DATE" DATE,
"CHECK_DATE" DATE,
"LAST_UPDATE_LOGIN" NUMBER,
"LAST_UPDATE_DATE" DATE,
"LAST_UPDATED_BY" VARCHAR2(60 BYTE),
"CREATED_BY" VARCHAR2(60 BYTE),
"CREATION_DATE" DATE,
"TAXABLE_PER_DIEM" NUMBER,
"NON_TAXABLE_MEALS" NUMBER,
"NON_TAXABLE_LODGING" NUMBER,
"OPS_PAY" NUMBER,
"STATUS_FLAG" VARCHAR2(1 BYTE),
"ERROR_MESSAGE" VARCHAR2(2000 BYTE)
)
Here is my control file:
OPTIONS (SKIP=0)
LOAD DATA
INFILE *
TRUNCATE INTO TABLE gross_payroll_amt_stg
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPLOYEE_NUMBER "LTRIM(RTRIM(:EMPLOYEE_NUMBER))",
GROSS_PAYROLL_AMT "LTRIM(RTRIM(:GROSS_PAYROLL_AMT))",
TAXABLE_PER_DIEM "LTRIM(RTRIM(:TAXABLE_PER_DIEM))",
NON_TAXABLE_MEALS "abs(LTRIM(RTRIM(:NON_TAXABLE_MEALS)))",
NON_TAXABLE_LODGING "abs(LTRIM(RTRIM(:NON_TAXABLE_LODGING)))",
OPS_PAY "TO_NUMBER(substr(:OPS_PAY,1,(instr(:OPS_PAY, '.') + 2)),'9999.99')",
CREATION_DATE SYSDATE,
CREATED_BY "fnd_global.user_id",
LAST_UPDATE_DATE SYSDATE,
LAST_UPDATE_LOGIN "fnd_global.login_id",
LAST_UPDATED_BY "fnd_global.user_id",
REC_ID "Gross_Pay_seq.nextval"
)
*Here is my data:*
68,1849.92,0,-210,-315,0
105,2692.31,0,0,0,3500
5847,3269.23,0,0,0,419.98
*Here is the error message:*
Record 1: Rejected - Error on table GROSS_PAYROLL_AMT_STG, column OPS_PAY.
ORA-01722: invalid number
OPS_PAY is the sixth colum (the 0 after the -315 value). Unfortunately I have to do the substr/instr (or something similar in the event you have a better idea) because the source system provides "whole" dollar amounts as 3500 (without a decimal point, but with the 00 cents) for $35.00....yet, in this same file the source system (for this same column - OPS_PAY) provides 419.98 (with the decimal point) when OPS_PAY is dollars and cents ($419.98).
Also I have thousands of rows similar to row 1 (where column 6 - OPS_PAY) equals 0. I've just truncated these additional rows for brevity.
I'm at my wits end here. I'll take any suggestions. Thanks in advance for the help !!
Jeff