在使用EXTERNAL TABLES将数据加载到Oracle 11g R2时，我收到以下错误。
error processing column DATE_M in row 1 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 2 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 3 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 4 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01847: day of month must be between 1 and last day of month
error processing column DATE_M in row 5 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 6 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 7 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 8 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 9 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
error processing column DATE_M in row 10 for datafile C:appSadminorcldpdumpTABLE_EXT.txt
ORA-01843: not a valid month
外部表创建：
CREATE TABLE TABLE_EXT
( "COMPANY" VARCHAR2(101),
"COMPANY_VN" VARCHAR2(15),
"IL" VARCHAR2(17),
"TERMINAL" VARCHAR2(8),
"T_NO" VARCHAR2(15),
"NAME" VARCHAR2(108),
"SNAME" VARCHAR2(50),
"REF_NO" VARCHAR2(23),
"AMOUNT" NUMBER(15,2),
"DATE_M" DATE,
"TIME" VARCHAR2(11),
"TEL_NO" VARCHAR2(25),
"ADDRESS" VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
COMPANY,
COMPANY_VN,
IL,
TERMINAL,
T_NO,
NAME,
SNAME,
REF_NO,
AMOUNT decimal ,
DATE_M CHAR date_format DATE mask "dd.mm.yyyy" ,
TIME,
TEL_NO,
ADDRESS
)
)
LOCATION ('TABLE_EXT.txt')
)
REJECT LIMIT 10
样本数据：
CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420,12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810,00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12,00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40,10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42,64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42,40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12,00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12,00|06.06.2011|08:46|0806068076700|06
NLS_DATE_FORMAT : DD/MM/RRRR
注意：我尝试了以下操作，但没有成功：
DATE_M DATE "dd.mm.yyyy"，
DATE_M CHAR date_format DATE mask "DD/MM/RRRR"
首先，您需要在单引号内而不是双引号内指定日期格式掩码。
其次，您为"AMOUNT"列指定的格式不正确，这相当令人困惑。当您说数据是"DECIMAL"时，实际上是在输入文件中指定二进制格式。您应该在访问参数中为FIELD定义指定字符，并让Oracle将字段转换为数字。我很少（如果有的话）使用CHAR以外的任何格式来描述SQL*Loader或外部表中的输入数据。然而，也就是说，你有点拘泥于此，因为我认为你不能在外部表定义中指定十进制字符（在你的情况下是'，'）。您只能通过NLS实例参数对其进行更改。
以下是我所做的（注意：10g数据库），以使您的示例起作用：
CREATE TABLE TABLE_EXT ( "COMPANY" VARCHAR2(101)
, "COMPANY_VN" VARCHAR2(15)
, "IL" VARCHAR2(17)
, "TERMINAL" VARCHAR2(8)
, "T_NO" VARCHAR2(15)
, "NAME" VARCHAR2(108)
, "SNAME" VARCHAR2(50)
, "REF_NO" VARCHAR2(23)
, "AMOUNT" NUMBER(15,2)
, "DATE_M" DATE
, "TIME_M" VARCHAR2(11)
, "TEL_NO" VARCHAR2(25)
, "ADDRESS" VARCHAR2(50) )
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_tables
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( COMPANY
, COMPANY_VN
, IL
, TERMINAL
, T_NO
, NAME
, SNAME
, REF_NO
, AMOUNT
, DATE_M CHAR date_format DATE mask 'dd.mm.yyyy'
, TIME_M
, TEL_NO
, ADDRESS ) )
LOCATION ('TABLE_EXT.txt') )
REJECT LIMIT 10 ;
我稍微修改了您的数据，将您的十进制字符改为"在数据中：
CITY HOSPITAL|04680072124|CITY|00614860|47746244218|JOHN|WHITE|172871|420.12|21.08.2011|14:26|0806422627784|06
CITY HOSPITAL|04680072124|CITY|00614847|14274017676|BRAD|BROWN|448127|810.00|22.08.2011|11:04|0806427488476|06
CITY HOSPITAL|04680072124|CITY|00614842|16218778886|PETER|BALSON|862626|12.00|24.08.2011|14:16|0806062177008|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|688811|40.10|24.08.2011|08:48|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614846|14607666866|GEORGE|LOUIS|460481|42.64|24.08.2011|08:47|0806424172468|06
CITY HOSPITAL|04680072124|CITY|00614860|18460662462|JR|TEPE|404622|44,16|22.04.2011|20:08|0806446446866|06
CITY HOSPITAL|04680072124|CITY|00614840|47207688618|BARRY|HRAN|402886|42.40|27.08.2011|11:12|0806478768007|06
CITY HOSPITAL|04680072124|CITY|00614847|42161048612|TOM|HIGGS|148640|12.00|06.06.2011|08:18|0806068076700|06
CITY HOSPITAL|04680072124|CITY|00614846|42161048612|TOM|HIGGS|208847|12.00|06.06.2011|08:46|0806068076700|06
这对我有效。
总之，我认为您需要（1）修改数据，（2）修改实例NLS参数以更改十进制字符，或者（3）将数据加载为字符，并在外部表顶部的视图中使用to_NUMBER函数。