Hi ToM,
i have the following external table definition
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
I have no problem reading the folowing record
209PC3127K7NI31N|I|01/10/2020||INC|SWIFT|PAYMENT|RETURNED|30|EUR|30|
but the following record is rejected
209M73530AWOGFFZ|I|01/10/2020|01225066|INC|SWIFT|PAYMENT|COMPLETE|1373|USD|1212.68|30.82
The only difference between the two records is the last field of the rejected record is populated and there's no record delimiter afterwards
Any suggestions how to change the table's definition in order not to reject records like the abovementioned?
Sorry I can't reproduce the problem:
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 <null> INC SWIFT PAYMENT RETURNED 30 EUR 30 <null>
209M73530AWOGFFZ I 01/10/2020 01225066 INC SWIFT PAYMENT COMPLETE 1373 USD 1212.68 30.82
Please provide an example file that fails (created using utl_file as I've done above) to show your problem