New External Table Functionality - PreProcessing and Data Pump
By Jean-Pierre Dijcks-Oracle on Feb 25, 2009
I'm not sure everyone is aware of the fact that you can use external tables these days to work with data pump export files. You can read in data using an external table, or you can unload data. For more information on this one read the actual syntax here.
Very recently, that is as in 22.214.171.124 of the RDBMS we also added something called pre-processing to the external table syntax. Here is a small syntax example in "pseudo code":
CREATE TABLE sales_external
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir1
(RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir: 'gunzip' OPTIONS '-c'
FIELDS TERMINATED BY '|'
For some more info on the actual syntax, I noticed Greg wrote a full example in his posting here.
The idea of pre-processing is to allow you to execute OS commands such as unzip directly in the External Table. That means that a zipped file coming in to save space can be easily loaded without coding additional shell scripts into the load process.
Give it a try, should be quite handy in many situations. From a compatibility perspective, be aware that this is in 126.96.36.199, but the plan is to get it put into 10.2.0.5 once that is released.