Oracle 12.2 New Features for Developers – External Tables

Hi,

We continue this series of blogs on New Features for Developers with some updated syntax to external tables.

External tables are great and they have been around for a while. They are basically a relational template for a file on the operating system. In the past, they have been read-only. You set them up much like a data loader (see my blog back in January 2015 on this topic) and simply select from them. Oracle will open the file and process it row at a time as per your SQL.

These are useful instead of loading a staging table, to just select from this flat file for your batch job perhaps.

Oracle12.2 has expanded this to allow you to change the location of this O/S file on the fly! The new syntax includes allowing you to change the access and reject parameters on the SELECT statement. The ‘CREATE TABLE with the ORGANIZATION EXTERNAL clause still has to happen first but you can alter these parameters on the fly.

Select * from external emp EXTERNAL MODIFY (
     [ DEFAULT DIRECTORY ]
     [ LOCATION ]
     [ ACCESS PARAMETERS ]
     [ REJECT LIMIT ]
);

Oracle VP Tirthankar Lahiri on the Oracle12.2 release date: “Oracle is presenting features for Oracle database 12c Release 2 on Oracle cloud. Features and enhancements related to the on-premises versions of Oracle Database 12c Release 2 are not being announced at this time”.

Dan Hotka
Oracle ACE Director
Author/Instructor/CEO