Data Loading and Unloading - Expert Oracle Database Architecture - page 712

Next, we'll create three test files, compress them, and place each in a separate directory:
$ echo 'base col1,base col2' | gzip > /tmp/base/filebase.csv.gz
$ echo 'base2a col1,base2a col2' | gzip > /tmp/base/base2a/filebase2a.csv.gz
$ echo 'base2b col1,base2b col2' | gzip > /tmp/base/base2b/filebase2b.csv.gz
Next, two directory objects are created—one that points to the directory that will contain a shell script and
another one that points to a base directory that serves a starting point for searching for files to be processed:
EODA@ORA12CR1> create or replace directory exec_dir as '/orahome/oracle/bin';
Directory created.
EODA@ORA12CR1> create or replace directory data_dir as '/tmp';
Directory created.
Next, a shell script is created named search_dir.bsh . The file is located in the /oracle/home/bin directory in
this example. The following code is placed within the script:
#!/bin/bash
/usr/bin/find $* -name "*.gz" -exec /bin/zcat {} \; | /usr/bin/cut -f1 -d,
The prior script will search starting in a directory that gets passed into the script; it will search the base directory
and all subdirectories underneath it for files with the extension .gz . And then for each file that is found, zcat is used to
view uncompressed data. Finally, cut displays just the first column of data.
And this makes the shell script executable:
$ chmod +x search_dir.bsh
Now all we need is an external table the uses search_dir.bsh to display data in the compressed files:
EODA@ORA12CR1> create table csv
2 ( col1 varchar2(20)
3 )
4 organization external
5 (
6 type oracle_loader
7 default directory data_dir
8 access parameters
9 (
10 records delimited by newline
11 preprocessor exec_dir:'search_dir.bsh'
12 fields terminated by ',' ldrtrim
13 )
14 location
15 (
16 data_dir:'base'
17 )
18 )
19 /
Table created.