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 (
     [ LOCATION ]

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

About the Author

Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Start the discussion at forums.toadworld.com