LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon