New External Table Functionality - PreProcessing and Data Pump

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 11.1.0.7 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
(…)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir: 'gunzip' OPTIONS '-c'
FIELDS TERMINATED BY '|'
)
LOCATION (…)
)

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 11.1.0.7, but the plan is to get it put into 10.2.0.5 once that is released.

Comments:

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today