error when trying to create external table

I'm a bit of an SQL beginner, and I'm trying to create an external table. Here's the SQL I have for it:

CREATE TABLE BACKEND.EXT_DATA_LOAD
(
  CUST_SESSIONID            VARCHAR2(255 BYTE),
  CUST_SESSION_DATE         DATE,
  CUST_ID                   VARCHAR2(10 BYTE),
  CUST_FAV_NUMBER           NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY INCOMING_DIR
     ACCESS PARAMETERS 
       (        RECORDS DELIMITED BY NEWLINE
       BADFILE 'custData.bad'
       NODISCARDFILE
       LOGFILE 'custData.log'
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"' AND '"'
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         CUST_SESSIONID             char,
         CUST_SESSION_DATE          char date_format DATE MASK 'dd/mm/yyyy',
         CUST_ID                    char,
         CUST_FAV_NUMBER            char
       ) )
     LOCATION (INCOMING_DIR:'custData.csv')
  )
REJECT LIMIT Unlimited
NOPARALLEL
MONITORING;

ALTER TABLE BACKEND.EXT_DATA_LOAD
 ADD PRIMARY KEY
  (CUST_SESSIONID)
ENABLE VALIDATE;

I thought maybe there was a problem with having the CUST_FAV_NUMBER as a NUMBER in the top portion, but char in the Access Parameters, but every example I've found has it set up the same way. When I try running this it says it fails on line 3 with the error

ORA-30657: Operation not supported on external organized table.

I can't see anything wrong with what I have, what am I missing?

Solution

I think the initial problem is monitoring: there are tricks for setting statistics on external tables but I don't think the default monitoring behavior works.

" I don't understand why though"

Monitoring is a background process Oracle runs to track change in a table - inserts, updates, deletes - so it can assess the staleness of gathered stats. When there's been sufficient change we can refresh the stats. Oracle can't do that tracking for external tables because the data is in an OS file. It can tell that the whole file has changed, but that is all.

Once you remove the monitoring clause you'll find that we can't put a primary key constraint on an external table, because primary keys require a not null check constraint. Obviously the database cannot enforce such a check constraint because the data is in a file on the OS, not in the actual database. The data in the external can be replaced at any time by over-writing the OS file.

For the exact same reason it's not possible to build an index on external tables (which is another reason why adding a primary key would fail).