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- 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
Make the parameters read as follows (Oracle OS user name and group):
run_user = oracle
run_group = oinstall
In some world-readable directory, create your shell script. My test shell script was test.sh, reading as follows:
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.
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;
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.