Preprocessing Input Files and External Tables

November 20, 2008 Oracle Database

Just a quick note to point to Greg Rahn’s posting on External Table Preprocessors in Oracle 11g 11.1.07. Prior to, if a file that you were going to use via an external table needed uncompressing, you had to do the task prior to reading it using the external table, potentially ending up with an uncompressed file several times larger than the compressed one, or use SQL*Loader and feed data into it via an uncompression utility and named pipes.

Now in 11g, you can add a PREPROCESSOR clause to your external table DDL to point to an executable that will preprocess and pipe your data, allowing the external table to read from the file via the uncompression utility without having to uncompress it all first. It’s one of those things that make you think “why wasn’t that there before?”, as Greg says though it’s now in albeit sketchily documented, so if you’re planning to move large files around as part of your ETL proces and you’re on the latest version of the database, it’ll be worth taking a look at Greg’s article.