External Tables from Shell Scripts

Here’s a little example of something I found quite interesting, and which you generally don’t see being used every day… Oracle has a concept known as “External Tables” which basically allows a table to be created inside the database which sources its content from outside the database.  This can be as simple as a .csv file, or more exciting (?) like a shell script.

oracle@slax:/tmp/exttab$ cat run_cmd.sh #!/bin/bash  /usr/bin/cat /usr/share/dict/words oracle@slax:/tmp/exttab$ sqlplus demo/demo  SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 20 16:24:31 2014  Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production  SQL> create directory xxtst as '/tmp/exttab';  Directory created.  SQL> create table xxext_tst (word varchar2(50))   2  organization external (   3    type oracle_loader   4    default directory xxtst   5    access parameters (   6      records delimited by newline   7      preprocessor xxtst:'run_cmd.sh'   8    )   9    location (  10      xxtst::'run_cmd.sh'  11    )  12  );  Table created.  SQL> select * from xxext_tst where rownum 
So to explain a little...
 
We have a shell script in /tmp/exttab which simply reads the contents of /usr/share/dict/words. Note you should not rely on your shell environment (i.e. $PATH variable) being set - always explicitly declare the location of files.
The first thing we do on the database is create a directory object. You may use an existing directory if you wish (query the dictionary view all_directories for a list).
We then create the table, which (in this instance) has a single column of varchar2(50), and we specify it as an EXTERNAL table (organization external). We also need to specify a few other options such as how records are delimited and which script to execute. If our table contained multiple rows then we would also need to specify the column delimiters (maybe I'll go through something a bit more complex in the future).
 
Obviously we can change the command(s) in the shell script and this is then reflected when selecting from the table:
 
oracle@slax:/tmp/exttab$ cat run_cmd.sh #!/bin/bash  /usr/bin/grep foo   select * from xxext_tst;  WORD --------------------------------------------------      1  afoot      2  archfool      3  barefoot      4  barefooted      5  buffoon  
 
You can of course execute things like "ps" to see currently running processes, "df" to query disk usage and so on... and it doesn't necessarily need to be a shell script. Exciting huh!?