1. Home
  2. Knowledge Base
  3. Database Administration
  4. Oracle 11g External Tables to store results of OS commands
  1. Home
  2. Knowledge Base
  3. Oracle 11g
  4. Oracle 11g External Tables to store results of OS commands

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

The following two tabs change content below.

Gavin Soorma

Latest posts by Gavin Soorma (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Comments

  1. Hi Gavin,
    great little posting! I did not think about using the preprocessor feature for that purpose yet, but it looks impressive 🙂

Leave a Comment