As of Oracle 12.2, you can modify some parameters of an external table at runtime. So instead of using ALTER TABLE one can use EXTERNAL MODIFY in a SQL statement. From Oracle 18c you don’t need to define an external table at all if you need to read a file.

So for example in 12.2 we can create an external table that points to a directory and filename. Then during runtime, we can modify the filename(location parameter).

REM create the ext table
create table ext_table (col1 number, col2 number)
organization external
( type oracle_loader
  default directory ext_table_dir
  access parameters
  ( records delimited by newline characterset we8mswin1252
    nobadfile
    nologfile
    fields terminated by ","
  )
  location ('myfile.txt')
)
reject limit unlimited;

Table EXT_TABLE created.

REM count number of rows
select count(*) from ext_table;

 COUNT(*)
----------
         3
REM Count the rows in another file in the same directory
SELECT count(*) FROM ext_table
     EXTERNAL MODIFY
     (LOCATION ('myfile2.txt'));

 COUNT(*)
----------
         1

The following can now be overridden on the fly since 12.2

  • DEFAULT DIRECTORY
  • LOCATION
  • ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE)
  • REJECT LIMIT

As of Oracle 18c, we don’t need to create an external table to read the file. We just need a directory and the privileges to access that directory.

 select count(*) from
    external (
       (
         col1 number, col2 number
      )
   type oracle_loader
   default directory ext_table_dir
   location ('myfile.txt')
   );

I think this is a really useful feature. We can do this in plain SQL without issuing any DDL!

Behind the scenes, Oracle seems to create a global temporary table, but that is another story.

Over & Out