Oracle 11g External Tables to store results of OS commands

In Oracle 11g, we can now use external tables to not only display data stored in flat files outside the database, but also store the result of any command executed at the OS level – for example in this case we have a shell script which basically runs the ‘uptime’ command and we then run that command from the database by just querying the table – this can have a number of useful applications and something that developers need to be made aware of …..

[oracle@redhat64 oracle]$ cat get_uptime.sh

#!/bin/ksh
/usr/bin/uptime

SQL> CREATE TABLE uptime
2 (
3 data varchar2(255)
4 )
5 ORGANIZATION external
6 ( TYPE oracle_loader
7 DEFAULT DIRECTORY load_dir
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE
10 preprocessor exec_dir:’get_uptime.sh’
11 FIELDS TERMINATED BY “|” LDRTRIM
12 )
13 location ( ‘get_uptime.sh’)
14 )
15 /

Table created.

SQL> select * from uptime;

DATA
——————————————————————————–
14:49:19 up 15 days, 4:44, 1 user, load average: 0.09, 0.16, 0.13