Database Reference
In-Depth Information
Now notice in the output that only the first column from the compressed files is displayed; this is because the
code in
search_dir.bsh
uses
cut
to only display the first column:
EODA@ORA12CR1> select * from csv;
COL1
--------------------
base2a col1
base col1
base2b col1
Notice how we can dynamically alter the directory location where we want the search for files to begin:
EODA@ORA12CR1> create or replace directory data_dir as '/tmp/base';
EODA@ORA12CR1> alter table csv location( 'base2a' );
Now when we select from the table, it returns just one record:
COL1
--------------------
base2a col1
Also, we can easily modify the shell script code and have it filter the data based on other criteria, such as filtering
rows by searching for a string within the CSV file (such as
base2
). Create a
search_dir2.bsh
script to do just that:
#!/bin/bash
/usr/bin/find $* -name "*.gz" -print0 | /usr/bin/xargs -0 -I {} /usr/bin/zgrep "base2" {}
And make it executable:
$ chmod +x search_dir2.bsh
Here a new external table definition allows for two columns in the output:
EODA@ORA12CR1> create table csv2
2 ( col1 varchar2(20)
3 ,col2 varchar2(20)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory data_dir
9 access parameters
10 (
11 records delimited by newline
12 preprocessor exec_dir:'search_dir2.bsh'
13 fields terminated by ',' ldrtrim
14 )
15 location
