Is it possible to create an external table with a numeric datatype looking at a file with a number format with a comma?
If I try and create a table looking at the following data, the table creates but the row gets rejected with "invalid number" for fieldb
fielda|fieldb
somedata|1,000
drop table xt_test;
CREATE TABLE XT_test
( fielda varchar2(10),
fieldb number
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY XT__LOGS
ACCESS PARAMETERS
( records delimited by newline
badfile XT__Bad:'testcsv.bad'
logfile XT__LOGS:'testcsv.log'
discardfile XT__LOGS:'testcsv.dsc'
skip 1
fields terminated by '|'
lrtrim
missing field values are null
( FIELDA CHAR(20) ,
FIELDB CHAR(20)
)
)
LOCATION (XT__LOGS:'test.csv')
)
REJECT LIMIT UNLIMITED NOMONITORING;
select * from XT_test;