Example: Wrap Oracle SQL in Unix Shell

The basic idea is to wrap SQL in a Unix shell language to generate SQL scripts. Use scripts to write more scripts, yeah...

#!/bin/ksh
#
# Make a two dimension array of time interval and successful 
# course registrations during the interval.  Intervals can be 
# second, minute, hour, day.

# Count up timestamps with associative array then filter with sort command.
counter () {
    /bin/nawk '
        {time_array[$0]++}
        END {for (time in time_array) print time, time_array[time] }' |
    sort -nr
}

# Usage message.
usage () {
   echo
   echo Usage: $0 's m h d date-string'
   echo "s=second, m=minute, h=hour, d=day"
   echo "date-string=11-MAR-98"
   echo Example: $0 'h 11-SEP-98'
   echo
   exit
} 

#######################################################
# Main

export ORACLE_HOME=/home/oracle/app/oracle/product/7.3.2
export ORACLE_SID=p_web

COMMENT=""
DAY=\'$2\'

case $1 in
  s) INTERVAL=\'YYYY-MM-DD:HH24:MI:SS\';;
  m) INTERVAL=\'YYYY-MM-DD:HH24:MI\';;
  h) INTERVAL=\'YYYY-MM-DD:HH24\';;
  d) INTERVAL=\'YYYY-MM-DD\'; COMMENT="--" ;;
  *) usage;;
esac

case $2 in
  "") usage;;
  *) ;;
esac

# format string in SQL: select to_char(entry_date,'YYYY-MM-DD:HH24:MI:SS')
sqlplus << EOF | counter
name/passwd  
    set pages 0 feed off echo off
    select to_char(entry_date,$INTERVAL)
            from feestmt
$COMMENT    where to_char(entry_date) = $DAY
            order by 1;
EOF
#################### cut here ######################