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 ######################

The views and opinions expressed in this page are strictly those of the page author.
The contents of this page have not been reviewed or approved by the University of Minnesota.