External Tables in Oracle Database 11g

Thanks to Oracle:

http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1106.pdf 

Examples Using the New PREPROCESSOR Access Parameter:
The following two examples demonstrate use of the preprocessor feature. In both examples, input data in compressed format is preprocessed to uncompress it and then sent to the ORACLE_LOADER access driver in uncompressed format. Example 1 specifies the preprocessor program directly on the PREPROCESSOR clause. Example 2 specifies the preprocessor program within a shell script, because the program uses additional arguments.
Example 1
This example requires you to take several distinct steps, including: supplying a preprocessor program; creating a simple data file, directory objects, and an external table; and then querying from that table to verify the data was successfully uncompressed.
Step 1 – Supply a preprocessor program
From within the shell, use the following command to copy the preprocessor program (in this case, zcat) to some other directory on your system.
% /bin/cp /bin/zcat /somedirectory/bin/zcat
Using the Preprocessor Feature with External Tables in Oracle Database 11g Release 2
3
Step 2 – Create a simple data file in compressed format
In another directory, create a simple data file named foo.dat containing the words “Hello World” and then use the gzip executable to compress the foo.dat file into a file named foo.dat.gz. Verify that the compressed file has been created by issuing an ls -l command.
% echo “Hello World” > foo.dat
% gzip foo.dat
% ls -l foo.dat.gz
-rw-rw-r-- 1 oracle dba 40 Oct 2 15:10 foo.dat.gz
Step 3 – Create directory objects and grant required privileges
Directory objects must be created for the directories that hold the preprocessor programs and data files. For this example, the necessary privileges on those directories are granted to user scott. The creation of directory objects and granting of privileges on them to only certain users is necessary for security reasons (see BEST PRACTICES). In the following examples, replace the name ‘somedirectory’ with the name of the directory to which you copied the zcat program in step 1. Replace the name ‘somedirectory1’ with the name of the directory in which you created the foo.dat.gz file in step 2.
SQL> create or replace directory execdir as '/somedirectory/bin';
Directory created.
SQL> create or replace directory data_dir as '/somedirectory1';
Directory created.
SQL> grant read, execute on directory execdir to scott;
Grant succeeded.
SQL> grant read, write on directory data_dir to scott;
Grant succeeded.
Step 4 – Create an external table named xtab
SQL> CONNECT scott/tiger
Connected.
SQL> CREATE TABLE xtab (COL1 varchar2(2000))
2 ORGANIZATION EXTERNAL (
3 TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY data_dir
5 ACCESS PARAMETERS (
Using the Preprocessor Feature with External Tables in Oracle Database 11g Release 2
4
6 RECORDS DELIMITED BY NEWLINE
7 PREPROCESSOR execdir:'zcat'
8 FIELDS (COL1 char(2000)))
9 LOCATION ('foo.dat.gz'))
10 REJECT LIMIT UNLIMITED
11 PARALLEL 2;
Table created.
Step 5 – Select from the external table to verify the data is uncompressed
SQL> SELECT * FROM xtab;
COL1
Hello World
1 row selected.
Example 2
The following example demonstrates using a shell script to uncompress the data. Shell scripts are necessary when preprocessor programs require additional arguments. Note that /bin/zcat and /bin/gunzip –c are functionally equivalent.
Step 1 – Create the preprocessor shell script
% echo ‘/bin/gunzip –c $1’ > uncompress.sh
% chmod +x uncompress.sh
% cp uncompress.sh /somedirectory/bin/uncompress.sh
Note the following when creating a preprocessor shell script:
• The full path name must be specified for system commands (for example, gunzip)
• The data file listed in the external table LOCATION clause should be referred to by $1. (On Windows systems, the LOCATION clause should be referred to by %1.)
• On Windows systems, the first line in the .bat file must be the following:
• @echo off
• Otherwise, by default, Windows will echo the contents of the batch file (which will be treated as input by the external table access driver).
Using the Preprocessor Feature with External Tables in Oracle Database 11g Release 2
5
• Make sure the preprocessor shell script has EXECUTE permissions
Steps 2 and 3 remain the same as in Example 1.
Step 4 – Create an external table named xtab
SQL> CONNECT scott/tiger
Connected.
SQL> drop table xtab;
Table dropped.
SQL> CREATE TABLE xtab (COL1 varchar2(2000))
2 ORGANIZATION EXTERNAL (
3 TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY data_dir
5 ACCESS PARAMETERS (
6 RECORDS DELIMITED BY NEWLINE
7 PREPROCESSOR execdir:'uncompress.sh'
8 FIELDS (COL1 char(2000)))
9 LOCATION ('foo.dat.gz'))
10 REJECT LIMIT UNLIMITED
11 PARALLEL 2;
Table created.
Step 5 – Select from the external table to verify the data is uncompressed
SQL> SELECT * FROM xtab;
COL1
Hello World
1 row selected.