Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722: Invalid Number using SQL*Loader when column value equals 0

user13111861Feb 25 2011 — edited Feb 26 2011
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 26 2011
Added on Feb 25 2011
1 comment
568 views