SQL Loader - Invalid Number

Question

I'm trying to load data via SQLoader, but it gives me error at the numeric field of Invalid Number

My Data File:

00163604~12002~S~N~N~Panasonic Juicer 1.5l Steel Color~ss~E~A~12/15/2014 3:33:57 PM~N~N~N~Y~294~SA

Control File:

LOAD DATA
INFILE "/home/dmf/ITEMLOC.txt"
APPEND
INTO TABLE DMF.MIG_ITEM_LC
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(
ITEM "SUBSTRB(:ITEM,1,25)",
LOC "TO_NUMBER(:LOC)",
LOC_TYPE "SUBSTRB(:LOC_TYPE,1,1)",
CLEAR_IND "SUBSTRB(:CLEAR_IND,1,1)",
TAXABLE_IND "SUBSTRB(:TAXABLE_IND,1,1)",
LOCAL_ITEM_DESC "SUBSTRB(:LOCAL_ITEM_DESC,1,250)",
LOCAL_SHORT_DESC "SUBSTRB(:LOCAL_SHORT_DESC,1,120)",
STORE_ORD_MULT "SUBSTRB(:STORE_ORD_MULT,1,1)",
STATUS_UPDATE_DATE sysdate,
STATUS "SUBSTRB(:STATUS,1,1)",
STORE_PRICE_IND "SUBSTRB(:STORE_PRICE_IND,1,1)",
RPM_IND "SUBSTRB(:RPM_IND,1,1)",
EXT_UIN_IND "SUBSTRB(:EXT_UIN_IND,1,1)",
RANGED_IND "SUBSTRB(:RANGED_IND,1,1)",
PRIMARY_SUPP "TO_NUMBER(:PRIMARY_SUPP)",  -- The Error is coming here
PRIMARY_CNTRY "SUBSTRB(:PRIMARY_CNTRY,1,3)"
)

Rejected - Error on table DMF.MIG_ITEM_LC, column PRIMARY_SUPP. ORA-01722: invalid number

If i write give constant to it, it loads successfully.

What could be the issue?

How is the PRIMARY_SUPP column defined in the database? Does it fail on all rows or just certain rows? Could the data contain a control character that is not visible? Oh and make sure none of your description columns contain your delimiter as that will throw everything off by a column. — Gary_W
It's defined as NUMBER in the database. and none of the description has the delimiter. When I give it a constant PRIMARY_SUPP CONSTANT '2', it gets loaded. but not with the value in the data file — Imran Hemani

Russell Reed Russell Reed · Accepted Answer · 2014-12-16T19:12:56

Your data as posted loads fine for me.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.2.0

Here's the create table statement I used:

create table test1
(
item varchar2(25),
loc number,
loc_type char(1),
clear_ind char(1),
taxable_ind char(1),
local_item_desc varchar2(250),
local_short_desc varchar2(120),
store_ord_mult char(1),
status char(1),
store_price_ind char(1),
rpm_ind char(1),
ext_uin_ind char(1),
ranged_ind char(1),
primary_supp number,
primary_cntry varchar2(3)
);

If you get this error while trying to load only the one record that you posted, then I would suspect an unprintable character, like @Gary_W suggested. View the data with a hex viewer to check.

A character set difference between the file and your NLS_LANG setting could be at fault, but I doubt it in this case, since your data looks to be all ASCII values.

SQL Loader - Invalid Number

1 Answers