News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 11g External Tables to store results of OS commands

  • Posted by Gavin Soorma
  • On March 16, 2010
  • 1 Comments

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

 

1 Comments

Uwe Hesse
  • May 31 2011
Hi Gavin, great little posting! I did not think about using the preprocessor feature for that purpose yet, but it looks impressive :-)

Leave Reply

Your email address will not be published. Required fields are marked *