External Table Columns

I have the following code. Now the issue is that we expect three columns in the file but sometime the other team sends us 4 columns. So instead of failing the load, it will load first three columns. When the file has less than 3 columns, then it fails which is expected. What logic do I need to place where it fails when an extra column is present in the file?

CREATE TABLE TESTING_DUMP (
"FIELD_1"            NUMBER,
"FIELD_2"            VARCHAR2(5),
"FIELD_3"            VARCHAR2(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
(
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  BADFILE "MY_DIR":"TEST.bad"
  LOGFILE "MY_DIR":"TEST.log"
  READSIZE 1048576
  FIELDS TERMINATED BY "|" LDRTRIM
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  (
  "LOAD"               CHAR(1),
  "FIELD_1"            CHAR(5),
  "FIELD_2"            INTEGER EXTERNAL(5),
  "FIELD_3"            CHAR(5)
  )
  )
  location
  (
  'Test.xls'
  )
)REJECT LIMIT 0;

File Test.xls has sample content below. Second line is correct. It should fail for first line but it does not.

|11111|22222|33333|AAAAA
|22222|33333|44444|

I wouldn't know how to do that in single step, so I'll suggest a workaround - see if it helps.

This is target table, which is - at the end - supposed to contain valid rows only:

SQL> create table ext_target
  2    (col1 number,
  3     col2 varchar2(5),
  4     col3 varchar2(5));

Table created.

External table contains only one column which will contain the whole row (i.e. no separate columns):

SQL> create table ext_dump
  2    (col  varchar2(100))
  3  organization external (
  4    type oracle_loader
  5    default directory ext_dir
  6    access parameters (
  7      records delimited by newline
  8      fields terminated by ','
  9      missing field values are null
 10      (
 11        col      char(100)    )
 12    )
 13    location ('test.txt')
 14  )
 15  reject limit unlimited;

Table created.

This is the whole file contents:

|11111|22222|33333|AAAAA
|22222|33333|44444|
|55555|66666|

External table contains the whole file (nothing is rejected):

SQL> select * from ext_dump;

COL
--------------------------------------------------------------------------------
|11111|22222|33333|AAAAA
|22222|33333|44444|
|55555|66666|

Insert only valid rows into the target table (so far, there are two conditions: there shouldn't be 4 "columns", and there can be only 4 | separators:

SQL> insert into ext_target (col1, col2, col3)
  2  select regexp_substr(col, '\w+', 1, 1),
  3         regexp_substr(col, '\w+', 1, 2),
  4         regexp_substr(col, '\w+', 1, 3)
  5  from ext_dump
  6  where regexp_substr(col, '\w+', 1, 4) is null
  7    and regexp_count(col, '\|') = 4;

1 row created.

The only valid row:

SQL> select * from ext_target;

      COL1 COL2  COL3
---------- ----- -----
     22222 33333 44444

SQL>

Now, you can adjust the where clause any way you want; what I posted is just an example.

How about adding a fourth field definition that maps to a datatype sure to fail, like a date column with a funky format sure not to be seen? The "MISSING FIELD VALUES ARE NULL" should render it NULL when not present, and the datatype conversion should error when it is present.