UNIX for Dummies Questions & Answers

#!/usr/bin/sh

# check argument passed to the script
if [ $# -ne 1 ]; then
   echo 'Illegal number of arguments.'
   exit 1
else
   DIR_NAME=$1
fi

#oracle variables
ORACLE_HOME=/path/oracle/client
export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
TNS_ADMIN=/path/tnsnames
export TNS_ADMIN

# put a list of .sql files in the given directory in a temporary file
ls -1 $DIR_NAME/*.sql >/tmp/sql_script_list.$$ 2>/dev/null
if [ $? -ne 0 ]; then
   echo "No .sql files found in $DIR_NAME"
   exit 1
fi

while read SQL_FILE; do
   OUT=/report/path/$SQL_FILE.output
   $SQLPLUS -s "user/pass@database" << END_SQL > /dev/null

      SET ECHO OFF
      SET TERMOUT OFF
      SET PAGESIZE 50000
      SET LINESIZE 32767
      SET TRIMSPOOL ON

      SPOOL $OUT

      @ $DIR_NAME/$SQL_FILE

      SPOOL OFF
END_SQL
done </tmp/sql_script_list.$$
rm /tmp/sql_script_list.$$