Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 06, 2021 - 10:02 pm UTC

Last updated: January 07, 2021 - 9:00 am UTC

Version: 12g

Viewed 1000+ times

You Asked

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?

and Chris said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.