Oracle – Capture OS Command Output Using Oracle External Tables

Case for the OS side:

Some things are best done on the OS side and there is no question about that.

Case for the DB side:

Some things are best done on the database side and there is no question about that either.

What if?

Some things need the output of one in the other (e.g., to consume outputs from the OS side on the DB side) to make a decision. I can think of a couple of uses although there are tons more:

  • Make decisions inside the database based on disk space availability in the OS host as of now
  • Run some complex OS script which returns some output that is needed in decision making on the DB side
  • Read from a compressed file (as shown in the reference below)

Today, we are going to see how we can take the output of dynamic commands (not static text files) run on the OS side and consume the output on the database side using Oracle external tables. The word “dynamic” signifies the fact that the output is produced and consumed as of the time the SELECT is done against the external table.

We already know about Oracle external tables, so I am going to skip that part and explain how to consume OS command output.

Capturing output of “ps-ef”

For this example let us say that I want to make decisions on the DB side based on the current output of the UNIX command “ps –ef”

Typically, the output will look like this:


UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Sep16 ?        00:00:13 init [3]
root         2     1  0 Sep16 ?        00:00:02 [migration/0]
root         3     1  0 Sep16 ?        00:00:00 [ksoftirqd/0]
root         4     1  0 Sep16 ?        00:00:02 [migration/1]
root         5     1  0 Sep16 ?        00:00:00 [ksoftirqd/1]
root         6     1  0 Sep16 ?        00:00:02 [migration/2]
root         7     1  0 Sep16 ?        00:00:00 [ksoftirqd/2]
root         8     1  0 Sep16 ?        00:00:02 [migration/3]
root         9     1  0 Sep16 ?        00:00:00 [ksoftirqd/3]
root        10     1  0 Sep16 ?        00:00:02 [migration/4]
root        11     1  0 Sep16 ?        00:00:00 [ksoftirqd/4]
root        12     1  0 Sep16 ?        00:00:01 [migration/5]
root        13     1  0 Sep16 ?        00:00:00 [ksoftirqd/5]
root        14     1  0 Sep16 ?        00:00:01 [events/0]
root        15     1  0 Sep16 ?        00:00:00 [events/1]
root        16     1  0 Sep16 ?        00:00:00 [events/2]

Will it work for my 10,000 line Shell Script?

Yes. The command that produced the above could have been a 10,000 line script but the procedure outlined here is the same nonetheless.  In other words, the output of the script could be any “data stream” so to speak, that the external table can then read from.

The steps to consume the OS script output are as follows:

  • Pick a folder that is accessible to user “oracle” (the owner)
  • Create a DIRECTORY object for the folder
CREATE OR REPLACE DIRECTORY utl_dir AS
'/mycompany.com/oracle/db/db1/utl/';
  • Grant appropriate rights for the DIRECTORY object to the Oracle user who will create the external table
GRANT READ,EXECUTE ON utl_dir TO ;
  • Create the script. In this case it is a simple script named “run_ps.sh” with these to lines
#/bin/bash
/bin/ps –ef
  • Make sure the script has execute privileges
chmod +x run_ps.sh
  • Try the script out
cd /mycompany.com/oracle/db/db1/utl/
./run_ps.sh

The output will look the same as the sample output above

  • Create an external table referencing the script as the source of data

Lazy man’s external table (with one column):

with one column to read an entire row’s worth of data into a single column

(use this if you want to get to the data real quick and you have a way of easily parsing out exactly what you want)

DROP TABLE external_table1;

CREATE TABLE external_table1 (entire_row_as_col VARCHAR2 (4000))
ORGANIZATION EXTERNAL
   (TYPE oracle_loader
     DEFAULT DIRECTORY utl_dir
     ACCESS PARAMETERS
     (
        RECORDS DELIMITED BY NEWLINE SKIP 1
        preprocessor utl_dir:'run_ps.sh'
        FIELDS TERMINATED BY '~'
     )
     LOCATION (utl_dir:'run_ps.sh')
   );

SELECT * FROM external_table1;
ENTIRE_ROW_AS_COL
root         1     0  0 Sep16 ?        00:00:13 init [3]
root         2     1  0 Sep16 ?        00:00:02 [migration/0]
root         3     1  0 Sep16 ?        00:00:00 [ksoftirqd/0]
root         4     1  0 Sep16 ?        00:00:02 [migration/1]
root         5     1  0 Sep16 ?        00:00:00 [ksoftirqd/1]
root         6     1  0 Sep16 ?        00:00:02 [migration/2]
root         7     1  0 Sep16 ?        00:00:00 [ksoftirqd/2]
root         8     1  0 Sep16 ?        00:00:02 [migration/3]
root         9     1  0 Sep16 ?        00:00:00 [ksoftirqd/3]
root        10     1  0 Sep16 ?        00:00:02 [migration/4]
root        11     1  0 Sep16 ?        00:00:00 [ksoftirqd/4]
root        12     1  0 Sep16 ?        00:00:01 [migration/5]
root        13     1  0 Sep16 ?        00:00:00 [ksoftirqd/5]
root        14     1  0 Sep16 ?        00:00:01 [events/0]
…more rows

External Table – The right way

(use this if you want something more permanent that will be part of a process)

DROP TABLE external_table2;

CREATE TABLE external_table2
(
    udid     VARCHAR2(50),
    pid     NUMBER,
    ppid    NUMBER,
    c       NUMBER,
    stime   VARCHAR2(8),
    tty     VARCHAR2(50),
    time    VARCHAR2(8),
    cmd     VARCHAR2(128)
)
ORGANIZATION EXTERNAL
(
     TYPE oracle_loader
     DEFAULT DIRECTORY utl_dir
     ACCESS PARAMETERS
     (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
           preprocessor  utl_dir:'run_ps.sh'
       READSIZE 1048576
       SKIP 1
       FIELDS TERMINATED BY WHITESPACE LDRTRIM
       REJECT ROWS WITH ALL NULL FIELDS
        (
            "UDID" CHAR(50) TERMINATED BY WHITESPACE,
            "PID" CHAR(50)  TERMINATED BY WHITESPACE,
            "PPID" CHAR(50)  TERMINATED BY WHITESPACE,
            "C" CHAR(50)  TERMINATED BY WHITESPACE,
            "STIME" CHAR(50)  TERMINATED BY WHITESPACE,
            "TTY" CHAR(50)  TERMINATED BY WHITESPACE,
            "TIME" CHAR(50)  TERMINATED BY WHITESPACE,
            "CMD" CHAR(50)  TERMINATED BY WHITESPACE
        )
     )
     location
     (
       utl_dir:'run_ps.sh'
     )
   );

SELECT * FROM external_table2;
UDID PID PPID C STIME TTY TIME CMD
root 1 0 0 16-Sep ? 0:00:13 init
root 2 1 0 16-Sep ? 0:00:02 [migration/0]
root 3 1 0 16-Sep ? 0:00:00 [ksoftirqd/0]
root 4 1 0 16-Sep ? 0:00:02 [migration/1]
root 5 1 0 16-Sep ? 0:00:00 [ksoftirqd/1]
root 6 1 0 16-Sep ? 0:00:02 [migration/2]
root 7 1 0 16-Sep ? 0:00:00 [ksoftirqd/2]
root 8 1 0 16-Sep ? 0:00:02 [migration/3]
root 9 1 0 16-Sep ? 0:00:00 [ksoftirqd/3]
root 10 1 0 16-Sep ? 0:00:02 [migration/4]
root 11 1 0 16-Sep ? 0:00:00 [ksoftirqd/4]
root 12 1 0 16-Sep ? 0:00:01 [migration/5]
root 13 1 0 16-Sep ? 0:00:00 [ksoftirqd/5]
root 14 1 0 16-Sep ? 0:00:01 [events/0]
root 15 1 0 16-Sep ? 0:00:00 [events/1]
…more rows
  • Create an view that joins the external table to any other view or table that you already have

References:

http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5088536900346242095

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s