Oracle 被外部表拒绝的记录

嗨，汤姆，

我有以下外部表定义

CREATE TABLE STAGE.EXT_TABLE1 (
FIELD1 VARCHAR2(16),  
FIELD2 VARCHAR2(2),  
FIELD3 VARCHAR2(10),  
FIELD4 VARCHAR2(16),   
FIELD5 VARCHAR2(10),  
FIELD6 VARCHAR2(15),  
FIELD7 VARCHAR2(8),  
FIELD8 VARCHAR2(25),  
FIELD9 NUMBER(19,2),
FIELD10 VARCHAR2(3),   
FIELD11 NUMBER(19,2),
FIELD12 NUMBER(19,2)   
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY CL_DATA_FOLDER
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE CL_BAD_FOLDER:'EXT_TABLE1.bad'
LOGFILE CL_LOG_FOLDER:'EXT_TABLE1.log'
FIELDS TERMINATED BY '|' optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
(
FIELD1 CHAR,   
FIELD2 CHAR,
FIELD3 CHAR,
FIELD4 CHAR,
FIELD5 CHAR,
FIELD6 CHAR,
FIELD7 CHAR,
FIELD8 CHAR,
FIELD9 CHAR,
FIELD10 CHAR,
FIELD11 CHAR,
FIELD12 CHAR
)    
)    
LOCATION ('EXT_TABLE1.TXT')
)    
REJECT LIMIT UNLIMITED NOPARALLEL

我读以下记录没有问题
209 pc3127k7ni31n | I | 01/10/2020 | 公司 | SWIFT | 付款 | 退回 | 30 | 欧元 | 30 |

但是以下记录被拒绝
209M73530AWOGFFZ | I | 01/10/2020 | 01225066 | INC | SWIFT | 付款 | 完成 | 1373 | 美元 | 1212.68 | 30.82

这两个记录之间的唯一区别是被拒绝记录的最后一个字段被填充，并且之后没有记录分隔符
有什么建议如何更改表的定义，以免拒绝上述记录？

抱歉，我无法重现该问题:

create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'EXT_TABLE1.TXT', 'w');
  utl_file.put_line(f, '209PC3127K7NI31N|I|01/10/2020||INC|SWIFT|PAYMENT|RETURNED|30|EUR|30|');
  utl_file.put_line(f, '209M73530AWOGFFZ|I|01/10/2020|01225066|INC|SWIFT|PAYMENT|COMPLETE|1373|USD|1212.68|30.82');
  utl_file.fclose(f);
end;
/

drop table EXT_TABLE1
  cascade constraints purge;
CREATE TABLE EXT_TABLE1 (
FIELD1 VARCHAR2(16),  
FIELD2 VARCHAR2(2),  
FIELD3 VARCHAR2(10),  
FIELD4 VARCHAR2(16),   
FIELD5 VARCHAR2(10),  
FIELD6 VARCHAR2(15),  
FIELD7 VARCHAR2(8),  
FIELD8 VARCHAR2(25),  
FIELD9 NUMBER(19,2),
FIELD10 VARCHAR2(3),   
FIELD11 NUMBER(19,2),
FIELD12 NUMBER(19,2)   
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE tmp:'EXT_TABLE1.bad'
LOGFILE tmp:'EXT_TABLE1.log'
FIELDS TERMINATED BY '|' optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
(
FIELD1 CHAR,   
FIELD2 CHAR,
FIELD3 CHAR,
FIELD4 CHAR,
FIELD5 CHAR,
FIELD6 CHAR,
FIELD7 CHAR,
FIELD8 CHAR,
FIELD9 CHAR,
FIELD10 CHAR,
FIELD11 CHAR,
FIELD12 CHAR
)    
)    
LOCATION ('EXT_TABLE1.TXT')
)    
REJECT LIMIT UNLIMITED NOPARALLEL;

select * from EXT_TABLE1;

FIELD1              FIELD2    FIELD3        FIELD4      FIELD5    FIELD6    FIELD7     FIELD8      FIELD9    FIELD10    FIELD11    FIELD12   
209PC3127K7NI31N    I         01/10/2020          INC       SWIFT     PAYMENT    RETURNED           30 EUR                30      
209M73530AWOGFFZ    I         01/10/2020    01225066    INC       SWIFT     PAYMENT    COMPLETE         1373 USD           1212.68      30.82 

请提供一个失败的示例文件 (使用utl_file创建，正如我上面所做的那样) 来显示您的问题