oracle external tables - ghdrako/doc_snipets GitHub Wiki

 create or replace directory f1db_csv
 as '/media/sf_Ergast_F1/f1db_csv'
 /

 create table drivers_ext 
 ( driverid  number  (  11 )
 , driverref varchar2( 256 )
 , driver_number varchar2( 256 )
 , code  varchar2(   4 )
 , forename varchar2( 256 )     
 , surname  varchar2( 256 )      
 , dob   date         
 , nationality   varchar2( 256 )
 , url    varchar2( 256 )
 ) organization external
 (
 type oracle_loader
 default directory f1db_csv
 access parameters
 ( -- you can use comments,-- but only at the beginning the parameters
 records delimited by newline
 skip 1
 badfile 'drivers.bad'
 logfile 'drivers.log'
 fields terminated by ','
 optionally enclosed by '"'
 ( driverid
 , driverref
 , driver_number
 , code
 , forename
 , surname
 , dob          
char( 10 ) date_format date mask "YYYY-MM-DD"
 , nationality
 , url
 )
 )
 location ( 'drivers.csv' )
 )
 reject limit 0
/

You can modify the settings after you create the external table, like pointing it to another file.

 alter table drivers_ext location ( 'drivers100.csv' );

If you change the access parameters, be aware that every parameter you don’t supply reverts to its default. It doesn’t retain the setting you gave it earlier.

Oracle Database 12.2 introduced the option to modify some settings at runtime.

  • You can change the filenames for the badfile, logfile, and discardfile in the access parameters. The other access parameters retain their values.
  • Change the default directory; it must be a literal value.
  • Change the location; it can be a literal value or a bind variable.
  • Change the reject limit; it can be a literal value or a bind variable.
 select count(*)  from   drivers_ext external modify (location ('drivers100.csv'))
 COUNT(*)
---------
100
 select count(*) from   drivers_ext
 /
 COUNT(*)
---------
854

Since Oracle Database 18c, you can access external tables without creating an external table. All the code normally in the DDL for an external table can now be put inside the SQL statement.

select * from external (
 ( constructorId  number(11)
 , constructorRef varchar2(256)
 , name           varchar2(256)
 , nationality   varchar2(256) 
, url         varchar2(256)   
)
 type oracle_loader
 default directory f1db_csv
 access parameters
 ( records delimited by newline
 skip 1
 badfile 'constructors.bad'
 logfile 'constructors.log'
 fields terminated by ','
 optionally enclosed by '"'
 ( constructorId
 , constructorRef
 , name
 , nationality
 , url
 )
 )
 location ( 'constructors.csv' )
 reject limit 0
 )

If you previously loaded data into Oracle Database using the SQL*loader utility, you can generate the needed external table definition using the EXTERNAL_TABLE=GENERATE_ONLY option.