OraFAQ Forum: Server Utilities » sqlldr: what's wrong?

Home » RDBMS Server » Server Utilities » sqlldr: what's wrong?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr: what's wrong? [message #224642]

Wed, 14 March 2007 21:18

syang
Messages: 30
Registered: February 2007

Member

Hi All,

I need to load some data into a table, and kept getting some error.

Record 1: Rejected - Error on table CBSSR.DMEPOS_CBA_PRODUCT_ITEM, column LAST_CHG_DT.
ORA-01858: a non-numeric character was found where a numeric was expected

I am still puzzled as why I got this error. Can someome advice as to what I did it wrong?

Thanks!

1. The actual data:

104,K0800,2,"Power operated vehicle, Group 1 Standard",02,,1,292.77,xxx@yahoo.com,3/13/2007
105,K0801,2,"Power operated vehicle, Group 1 Heavy Duty",02,,2,084.22,yyyy@hotmail.com,3/13/2007

2. The table defintion:

SQL> desc dmepos_cba_product_item;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ITM_ID NOT NULL NUMBER(38)
HCPCS_CD CHAR(5)
PROD_CTGRY_ID NOT NULL NUMBER(38)
PROD_ITM_DESC VARCHAR2(800)
PROD_PRCMT_TYPE_CD CHAR(2)
PROD_WT_QTY NUMBER(11,10)
FEE_SCHDL_PRICE_AMT NUMBER(38)
LAST_CHG_USER_ID VARCHAR2(80)
LAST_CHG_DT DATE

3. The control file:

load data
infile dmepos_cba_product_item_fix_3.dat
append
into table cbssr.dmepos_cba_product_item
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
PROD_ITM_ID,
HCPCS_CD,
PROD_CTGRY_ID,
PROD_ITM_DESC CHAR(800),
PROD_PRCMT_TYPE_CD,
PROD_WT_QTY,
FEE_SCHDL_PRICE_AMT
LAST_CHG_USER_ID,
LAST_CHG_DT date "mm/dd/yyyy HH:MI:SS AM"
)

Report message to a moderator