SQL Loader Control file - representing decimals
626103Feb 25 2010 — edited Feb 25 2010Hi,
I am using Oracle database version 10.2.0. I will be receiving a pipe delimited data file which I need to load into the table specified below. Can you please suggest me how to represent the last column ERROR_PERCENT in control file, which is number(3,2) in table? I tried DECIMAL(3,2), DECIMAL EXTERNAL(3,2) against it and I wasgetting errors. Please help.
CREATE TABLE TRACK_ERRORS
(
ACCOUNT_ID NUMBER(7),
DATESTAMP TIMESTAMP(6),
SPECIFIC_RISK NUMBER(3),
STOCK_RISK NUMBER(3),
ERROR_PERCENT NUMBER(3,2)
)
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\Sqlldr\Input\Track_Errors.dat'
BADFILE 'C:\Sqlldr\Track_Errors.bad'
DISCARDFILE 'C:\Sqlldr\Track_Errors.dis'
APPEND
INTO TABLE "TRACK_ERRORS"
fields terminated by '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(Account_Id INTEGER EXTERNAL(7)
,datestamp "Rtrim(to_date(:datestamp,'YYYY/MM/DD'))"
,Specific_Risk INTEGER EXTERNAL(3)
,Stock_Risk INTEGER EXTERNAL(3)
,ERROR_PERCENT
)
Regards,
Naveen