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
- 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