Shell scripting

This article contains some shell scripting examples:

Set the evironment

Use the follwoing code to setup the environment before you execute any Oracle utilities:

export ORACLE_SID=yoursid
export ORAENV_ASK=NO;
. /usr/local/bin/oraenv
export ORAENV_ASK=YES;

Capture SQL*Plus output

Some examples of how to interact with Oracle from a Unix or Linux shell script and return back to the shell script:

OUTPUT=`sqlplus -s scott/tiger <<END
      SELECT * FROM dual;
      EXIT 0;
END`

ERROR=`echo $OUTPUT | grep ORA-`

if [ "$ERROR" = "" ]; then
  echo 'No error - continue'
else
  echo 'Error - stop!'
fi

Loop through databases on a server

All databases on your machine should be listed in the oratab file. One can use this information to do processing on all databases. Look at this example:

#!/bin/ksh
ALL_DATABASES=`cat /etc/oratab|grep -v "^#"|grep -v "N$"|cut -f1 -d: -s`
for DB in $ALL_DATABASES
do
   unset  TWO_TASK
   export ORACLE_SID=$DB
   export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
   export PATH=$ORACLE_HOME/bin:$PATH
   echo "---> Database $ORACLE_SID, using home $ORACLE_HOME"
   sqlplus -s system/${DB}password @<<-EOF
select * from global_name;
exit;
EOF
done

NOTE: The terminating 'EOF' should normally be in column 1. The '-' before the first EOF above causes the shell to strip all leading TAB characters (not spaces, only TABs) from that point until (and including the EOF). This lets you indent the code in between using TABs.