Home » RDBMS Server » Server Utilities » sqlldr: what's wrong?
sqlldr: what's wrong? [message #224642] Wed, 14 March 2007 21:18 Go to next message
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"
)

Re: sqlldr: what's wrong? [message #224644 is a reply to message #224642] Wed, 14 March 2007 21:29 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can someome advice as to what I did it wrong?
The data in the input file does NOT match the date format in the control file.
Previous Topic: sqlldr error
Next Topic: SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help !
Goto Forum:
  


Current Time: Thu Dec 08 04:05:21 CST 2016

Total time taken to generate the page: 0.13628 seconds