How to Pass Arguments to OS Shell Script from Oracle Database

Imagine you have several Oracle databases on the same host under same OS user.

In scripts directory you have shell script that kills OS processes.
Idea is to call OS script from database procedure and kill problematic process using shell script.

Script will run simple query to get process id and kill that process.

But how to assure that this script will execute in correct environment for correct database?

One way is to create one script per database and set environment inside the script, or create just one script which will dynamically set correct environment for instance that is calling script.

For demo case I’ve created simple script that spools query output to the file.

#!/bin/bash

# Avoid oraenv asking
ORAENV_ASK="NO"; export ORAENV_ASK

ORACLE_SID=$1; export ORACLE_SID

. oraenv ${ORACLE_SID}

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF > /tmp/my_environment.txt
set heading off feedback off verify off
col instance_name for a10
col host_name for a10
col status for a10
select instance_name, host_name, status
 from v\$instance;
exit
EOF

$ chmod u+x simple_script.sh

What happens when we execute script.

$ ./simple_script.sh testdb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 is /u01/app/oracle
$
$ cat /tmp/my_environment.txt

testdb     asterix    OPEN

$ ./simple_script.sh ora11gr2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 is /u01/app/oracle
$
$ cat /tmp/my_environment.txt

ora11gr2   asterix    OPEN

Notice how I specified ORACLE_SID using command line argument. Script sets environment from ORATAB file according to specified SID and spools output to my_enviroment.txt file.

I will demonstrate how to pass argument from database layer.


To execute external job I have to create credentials on both databases.

-- Session 1

system@ORA11GR2> begin
  2      dbms_scheduler.create_credential(
  3      credential_name => 'ORACLE_CRED',
  4      username => 'oracle',
  5      password => 'password');
  6      end;
  7  /

PL/SQL procedure successfully completed.


-- Session 2

system@TESTDB> begin
  2      dbms_scheduler.create_credential(
  3      credential_name => 'ORACLE_CRED',
  4      username => 'oracle',
  5      password => 'password');
  6      end;
  7  /

PL/SQL procedure successfully completed.

Use SYS_CONTEXT function to get instance name and execute script for specified instance.

-- Session 1

system@ORA11GR2> DECLARE
  2    l_oracle_sid varchar2(20);
  3  BEGIN
  4    select sys_context('userenv','instance_name') into l_oracle_sid
  5    from dual;
  6        DBMS_SCHEDULER.CREATE_JOB (
  7              job_name => 'J_SIMPLE_SCRIPT',
  8              job_type => 'EXECUTABLE',
  9              job_action => '/home/oracle/skripte/simple_script.sh',
 10              number_of_arguments => 1,
 11              start_date => NULL,
 12              repeat_interval => NULL,
 13              end_date => NULL,
 14              enabled => FALSE,
 15              auto_drop => TRUE,
 16              comments => 'Set environment and execute query on v$instance view');
 17           dbms_scheduler.set_attribute('J_SIMPLE_SCRIPT','credential_name','ORACLE_CRED');
 18           DBMS_SCHEDULER.set_job_argument_value('J_SIMPLE_SCRIPT',1,l_oracle_sid);
 19          DBMS_SCHEDULER.enable('J_SIMPLE_SCRIPT');
 20          DBMS_SCHEDULER.run_job (job_name=> 'J_SIMPLE_SCRIPT', use_current_session => FALSE);
 21  END;
 22  /

PL/SQL procedure successfully completed.


system@ORA11GR2> host cat /tmp/my_environment.txt

ora11gr2   asterix    OPEN

I’ve called script from "ora11gr2" database and OS script was executed for specified database. DBMS_SCHEDULER job was used for passing argument to external OS script and for script execution.

From another session.

-- Session 2

system@TESTDB> DECLARE
  2    l_oracle_sid varchar2(20);
  3  BEGIN
  4    select sys_context('userenv','instance_name') into l_oracle_sid
  5    from dual;
  6        DBMS_SCHEDULER.CREATE_JOB (
  7              job_name => 'J_SIMPLE_SCRIPT',
  8              job_type => 'EXECUTABLE',
  9              job_action => '/home/oracle/skripte/simple_script.sh',
 10              number_of_arguments => 1,
 11              start_date => NULL,
 12              repeat_interval => NULL,
 13              end_date => NULL,
 14              enabled => FALSE,
 15              auto_drop => TRUE,
 16              comments => 'Set environment and execute query on v$instance view');
 17           dbms_scheduler.set_attribute('J_SIMPLE_SCRIPT','credential_name','ORACLE_CRED');
 18           DBMS_SCHEDULER.set_job_argument_value('J_SIMPLE_SCRIPT',1,l_oracle_sid);
 19          DBMS_SCHEDULER.enable('J_SIMPLE_SCRIPT');
 20          DBMS_SCHEDULER.run_job (job_name=> 'J_SIMPLE_SCRIPT', use_current_session => FALSE);
 21  END;
 22  /

PL/SQL procedure successfully completed.


SQL> host cat /tmp/my_environment.txt

testdb     asterix    OPEN

Notice how "/tmp/my_environment.txt" file changed according to specified database.


Using this method you can easily reuse OS scripts for more databases.