Shell Script Output to Oracle Database Via External Table

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.

Sometimes code written in PL/SQL needs to test values in the operating system. However, Oracle tools such as sys_context do not support every possibility: you could not (for example) use sys_context or utl_inaddr to discover if the Internet Time Protocol was enabled.

Fortunately, there is a way to get the output of a shell script into an Oracle external table.

Here is an example with a trivial shell script. It is based on a Sun environment (and DB 10gR2) but could easily be adapted to Linux or another Unix. The steps are:

1. Set permissions on some files in Oracle bin.
2. Write a shell script in the right style.
3. Create some Oracle objects, mainly an external table and a Scheduler job.

Here goes.

PERMISSIONS:

As root,

1- In the Oracle home bin directory:

chown root:oinstall $ORACLE_HOME/bin/extjob
chmod 4750 $ORACLE_HOME/bin/extjob
chown oracle:oinstall $ORACLE_HOME/bin/extjobo
chmod 644 $ORACLE_HOME/bin/extjobo

2- In the Oracle home admin directory:

chown root:oinstall $ORACLE_HOME/rdbms/admin/externaljob.ora
chmod 644 $ORACLE_HOME/rdbms/admin/externaljob.ora
vi $ORACLE_HOME/rdbms/admin/externaljob.ora

Make the parameters read as follows (Oracle OS user name and group):

run_user = oracle
run_group = oinstall

SHELL SCRIPT:

In some world-readable directory, create your shell script. My test shell script was test.sh, reading as follows:

#!/bin/sh
ls >> /export/home/dclement/log_test/test.out

Both the shebang and the full path are important; when Oracle tries to execute the external job, it will not have a shell environment.

ORACLE OBJECTS:

1- Create an Oracle directory object that points to the file system directory where the shell script lives. I used DAVID_TEST_DIR.

    CREATE DIRECTORY david_test_dir AS '/export/home/dclement/log_test';

2- Create an Oracle external table to point to the output of the shell script:

    CREATE TABLE sch_program_t
    (
      ls_out  VARCHAR2(512)
    )
    ORGANIZATION EXTERNAL
      (  TYPE oracle_loader
         DEFAULT DIRECTORY david_test_dir
         ACCESS PARAMETERS
           (        RECORDS DELIMITED BY NEWLINE
           NOBADFILE
           NODISCARDFILE
           NOLOGFILE
           SKIP 0
           FIELDS TERMINATED BY WHITESPACE
           MISSING FIELD VALUES ARE NULL
           REJECT ROWS WITH ALL NULL FIELDS
           (
             ls_out CHAR
           ) )
        LOCATION (david_test_dir:'test.out')
      );

3- Define a scheduled program and a job to run it, as follows:

    BEGIN
       sys.DBMS_SCHEDULER.create_program (
          program_name          => 'sch_program'
         ,program_type          => 'EXECUTABLE'
         ,program_action        => '/export/home/dclement/log_test/test.sh'
         ,number_of_arguments   => 0
         ,enabled               => TRUE
         ,comments              => 'Test Program');

       sys.DBMS_SCHEDULER.create_job (
          job_name          => 'SCH_PROGRAM_J'
         ,start_date        => SYSTIMESTAMP AT TIME ZONE 'US/Pacific'
         ,repeat_interval   => 'freq=minutely; interval=1'
         ,enabled           => TRUE
         ,program_name      => 'SCH_PROGRAM');
    END;

RESULT:

The output file in the target directory will now be loaded with data from the operating system once a minute, and this data will be visible within Oracle in the external table.

Related articles:

• Neat Oracle Database 11g Release 2 Feature: Preprocessing External Tables
• Answer from Oracle about the PL/Scope Database Corruption Issue
• User Exceptions Gotcha, When an Error Appears as a Success

Filed in Oracle on 17 May 11 | Tags: dbms_scheduler, unix

---