How to read database table definition from Unix Shell?

A UNIX developer or an admin gets to refer to a database table to check for a particular field type, or to check for the columns of a table. Instead of connecting to sqlplus manually every time to get the column names or to connect to toad, we can write a simple shell script which will read or get the table definition. We will see in this article how to create a simple script for this requirement and use it like a command.
Let us write a script in a file named 'getTable':

#!/usr/bin/ksh

if [ $1 = "-c" ]; then
   cols=1
   table=$2
else
   cols=0
   table=$1
fi

USER="scott"
PASSWD="tiger"
INST="school"

x=`sqlplus -s $USER/$PASSWD@$INST  <<EOF
set heading off
spool result.txt
desc $table
spool off
EOF`

head -1 result.txt | grep -q ERROR

if [ $? -eq 0 ]; then
   echo Invalid table $table
   exit
fi

if [ $cols -eq 1 ]; then
  awk 'NR>2{print $1}' result.txt
else
  awk 'NR>2{printf "%-30s  %-15s\n", $1, !($4)?$2:$4;}' result.txt
fi

\rm result.txt

How to run the script:

1. To get the columns and field type's, say, for a table 'employee', the script 'getTable' can be run as shown below.(In order to know, how to run a script like a command, refer this.)

#getTable employee
EMP_ID         NUMBER(3)
EMP_NAME       VARCHAR2(20)
EMP_DOB        DATE

2. In order to get only the columns of the employee table:

#getTable -c employee
EMP_ID
EMP_NAME
EMP_DOB  

3. If one is looking for the definition of a particular field, you can do a 'grep' on the output as well.

#getTable employee | grep NAME
EMP_NAME         VARCHAR2(20)

#getTable employees
Invalid table employees