Listing 2: desc_dbase.sh

#!/bin/sh

# -----------------------------------------------------
# DESC_DBASE.SH                       December 29, 1994
# Scott A. Tarvainen                      Kyle V. Gleed
# -----------------------------------------------------
# This script provides a description of the database
# fields of Oracle databases.  This information is
# provided to the user via the vi editor or printed
# output.  The user is prompted to enter the database
# which is to be described.  The script queries the
# database and returns a description of all tables
# within that database.
# -----------------------------------------------------

#
# Procedure to check for SQL connect errors
#

error_check() {

if [ -s /tmp/sqlerror.log ]; then
if ( grep -i error /tmp/sqlerror.log \
> /dev/null 2>&1 );then
clear
echo
echo -n "Following ERROR encountered "
echo "during database connection:"
echo
head -1 /tmp/sqlerror.log
echo
exit 1
fi
else
clear
echo
echo "$USERID/<PASSWORD>@$DATABASE is empty"
echo
exit 1
fi
}

#
# Process temporary working files
#

if [ -f /tmp/dbstruct ]; then
rm /tmp/dbstruct
fi

if [ -f /tmp/sqlerror.log ]; then
rm /tmp/sqlerror.log
fi

#
# Prompt user to enter database and
# data field information
#

clear
echo
echo -n "Enter SQLPLUS arguments as outlined below "
echo "when prompted"
echo ">> e.g. sqlplus USERID/PASSWORD@DATABASE <<"
echo
echo -n "Note: the PASSWORD field will not be "
echo "displayed for security reasons"
echo

echo -n "Enter USERID ---> "
read USERID
USERID=`echo $USERID | tr [a-z] [A-Z]`

echo -n "Enter PASSWORD -> "

#
# Turn echo off so password won't be displayed
#

stty -echo
read PASSWORD
PASSWORD=`echo $PASSWORD | tr [a-z] [A-Z]`

#
# Turn echo back on
#

stty echo
echo
echo -n "Enter DATABASE -> "
read DATABASE
DATABASE=`echo $DATABASE | tr [a-z] [A-Z]`
echo
echo
echo -n "Working"

#
# Query the database to build a list of tables
#

sqlplus -s $USERID/$PASSWORD@$DATABASE << xxENDxx \
>/tmp/sqlerror.log 2>&1
set pause off
set feedback off
set heading off
set pages 0
set lines 300
spool /tmp/tables
select TABLE_NAME
from USER_CATALOG;
spool off;
quit
xxENDxx

error_check

#
# Define the structure of each Oracle table in the list
#

for TABLE in `cat /tmp/tables.lst`
do

echo -n "."

sqlplus -s $USERID/$PASSWORD@$DATABASE << xxENDxx \
> /tmp/sqlerror.log 2>&1
set pause off
set feedback off
set heading off
set pages 0
set lines 300
spool /tmp/work
desc $TABLE
spool off;
quit
xxENDxx

#
# Echo the Oracle table definition to a working file
#

echo -n "========================" >> /tmp/dbstruct
echo "===========================" >> /tmp/dbstruct
echo "          $TABLE           " >> /tmp/dbstruct
echo -n "========================" >> /tmp/dbstruct
echo "===========================" >> /tmp/dbstruct
echo " " >> /tmp/dbstruct
cat /tmp/work.lst | grep -v SPOOL >> /tmp/dbstruct
done

#
# Give the user option to either view
# or print the table definition file
# NOTE: Print option assumes the default printer is set
#

while :
do
clear
echo
echo -n "View or Print "
echo -n "the database structure? (V/P) -> "
read OPTION

case $OPTION in

[Vv])  vi /tmp/dbstruct
break
;;
[Pp])  lpr /tmp/dbstruct
break
;;
*)  echo "Invalid Option, try again."
sleep 2
continue
;;
esac
done

echo
echo "DESC_DBASE.SH Finished."
echo