Bash script to run an Oracle SQL query
Shell script that checks the Oracle environment and runs a typed query with sqlplus -s.
This Bash script asks for a username, a password and a SQL query, checks the Oracle environment, runs the query with SQL*Plus in silent mode and prints the result.
Script
#!/bin/bash
# Validate the value of ORACLE_HOME #
# If ORACLE_HOME is empty #
if [ -z $ORACLE_HOME ]
then
echo "Set the ORACLE_HOME variable"
exit 1
fi
# If ORACLE_HOME doesn't exist #
if [ ! -d $ORACLE_HOME ]
then
echo "The ORACLE_HOME $ORACLE_HOME does not exist"
exit 1
fi
# Validate the value of ORACLE_SID #
if [ -z $ORACLE_SID ]
then
echo "Set the ORACLE_SID variable"
exit 1
fi
sid_dir=`echo $ORACLE_HOME | sed -n 's@^\(\/[^\/]\+\/\).*$@\1@;p'`
# Check the given ORACLE_SID is valid.
if [ ! -d $sid_dir/oradata/$ORACLE_SID ]
then
echo "The ORACLE_SID is invalid"
exit 1
fi
# Enter the username and password to login to oracle #
echo "Enter the username"
read username
echo "Enter password"
stty -echo
read password
stty echo
# Get the query , no validation applied for query #
echo "Enter the query"
read query
# Login and execute the query.
echo "set feedback off verify off heading off pagesize 0
$query
exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
do
echo $output
done
Example
Given a table test with a numeric column SNO:
$ ./sql_query.sh
Enter the username
system
Enter password
Enter the query
select SNO from test;
12
23
34
45
Notes
- The query is executed as typed, with no validation.
- The script expects the data files in
<first directory of ORACLE_HOME>/oradata/<ORACLE_SID>; adapt the check if the layout differs.