November 20th, 2008 by Mark Rittman
Just a quick note to point to Greg Rahn’s posting on External Table Preprocessors in Oracle 11g 11.1.07. Prior to 188.8.131.52, 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 184.108.40.206 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.