SQLPlus from shell

This is my first script calling Oracle SQLPlus.

- The /nolog option takes care of hiding the password from ps and similar tools.

- The command line will be 'sqlplus -S user/password@host:port/SID' without /nolog.

- The line 'host sleep 60' can be used in the script to delay execution.

- The 'set' parameters can be grouped together.

- sqlplus should use encrypted connection

- Make sure the script itself is not readable by unauthorized people.

- The script is written in AIX ksh.

Some details were changed, the blocks are part of a working script. You are right if you think there are better ways for handling certain conditions, the script is being improved.

There are some Oracle variables required for sqlplus.

NLS_LANG=<optional>
ORA_NLS33=<optional>
ORACLE_BASE=<set either this or ORACLE_HOME>
ORACLE_HOME=<>
PATH=$PATH:<path to sqlplus> 
TNS_ADMIN= 
  export NLS_LANG ORA_NLS33 ORACLE_BASE ORACLE_HOME PATH TNS_ADMIN

As an example, we query an update timestamp from the DB, here, the time when a currency rate was updated.

Unfortunately, I'm still getting a blank line after each select result, so I must handle that later.

query_someapp1_update() { 
sqlplus -S /nolog <<EOF 
  connect someapp1/password@host:port/SID; 
  set echo off; 
  set heading off; 
  set newpage none; 
  set pagesize 0; 
  -- select distinct value1 from table1 where field1 = 'F1' and curr = 'EUR='; 
  select distinct value2 from table1 where field1 = 'F2' and curr = 'EUR='; 
  -- select to_char (sysdate,'DD MON YYYY') from dual; 
  select to_char (sysdate - 1/24,'HH24:MI') from dual; 
EOF 
}

Query someapp2's status 

query_someapp2_status() { 
sqlplus -S /nolog <<EOF 
  connect someapp2/password@host:port/SID;  
  set echo off; 
  set heading off; 
  set newpage none; 
  set pagesize 0; 
  select status from sometable; 
EOF 
}

This function will restart our standalone Java app, "someapp1" if the timestamp shows a defined delay.

restart_someapp1 () { 
if [ $(expr "$systemtime_in_mins" - "$APPLtime_in_mins") -ge "$allowed_diff" ]; then 
  echo "$(date +%F-%T) [INFO ] Restarting" 
   kill -9 $(ps -u someapp1 -o pid,comm | awk '/java/ {print $1}') 
   su - someapp1 -c ~/someapp1/start_someapp1.sh 
  else 
  echo "$(date +%F-%T) [INFO ] someapp1 check OK" 
fi 
}

Check if someapp2 should be open 

check_status() { 
if [ $(date +%H%M) -ge 1700 ] || [ $(date +%H%M) -le 0800 ]; then 
  # someapp2 is closed, not checking 
  continue 
  elif [ $(query_someapp2_open | xargs echo) -ne 1 ]; then 
  echo "$(date +%F-%T) [ERROR] someapp2 should be open, but it is closed." 
  else 
  echo "$(date +%F-%T) [INFO ] someapp2 check OK" 
fi 
}

The main part of the script. xargs comes in the picture, as I need the results on a single line. NB: whitespace is not handled here!

## Query the someapp1 update and the system time from the DB, join the result on a single line, store the result in an array 
set -A times $(query_someapp1_update | xargs echo | sed 's/\:/ /g') 
## Do nothing right after 00:00 to the amount of allowed time difference to avoid messing around with numbers 
if [ "${times[2]}""${times[3]}" -lt "$allowed_diff" ]; then 
  # This should evaluate to something like  '0008 is less than 10' 
  exit 0 
fi 
## Convert hours to minutes 
someapp1_time_in_mins=$(expr "${times[0]}" \* 60 + "${times[1]}") 
systemtime_in_mins=$(expr "${times[2]}" \* 60 + "${times[3]}")

Then comes the actual execution of the functions.