Unix Shell Script to Execute Oracle SQL Query

Question: Can you provide me a shell script that will accept oracle credentials, sql query to be executed and displays the output?

Answer: The shell script given below prompts some basic information and displays the output of the SQL.

You can use the same concept and hard-code some of these values in the shell-script itself and even run this script in the background to generate the output of an oracle sql query automatically (or use the oracle shell script from cron job).

This script accepts following values from the user:

• Oracle username
• Oracle password
• Oracle SQL Query to be executed.

Script validates the $ORACLE_HOME and $ORACLE_SID set in environment.

Shell Script to Execute Query in Oracle

This script allows you to enter the credential to login to oracle, and executes the query and displays the output.

$ cat sql_query.sh
#!/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

In database,

SQL> select * from test;

       SNO
----------
        12
        23
        34
        45

 $ ./sql_query.sh
Enter the username
system
Enter password
Enter the query
select SNO from test;
12
23
34
45

Tweet

Share

 Comment

If you enjoyed this article, you might also like..