Unix Shell Script to Execute SQL Query in Oracle

There are many ways to run SQL query using Unix shell script. Here we have posted a shell script that will accept oracle user credentials (username & password) and SQL query to execute and display the output of SQL.

You can run the script automatically in the background or use the cron job to generate the output of an Oracle SQL query. This script requires Oracle Credential (Username and Password), SQL query. This script automatically validates $ORACLE_HOME and $ORACLE_SID which is set in the environment.

Execute below Shell Script to generate SQL query output in Oracle

$ cat exec_sql_query.sh
#!/bin/bash
# It will 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 does not exist #
if [ ! -d $ORACLE_HOME ]
then
        echo "The ORACLE_HOME $ORACLE_HOME does not exist"
        exit 1
fi
# It will 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@^\(\/[^\/]\+\/\).*$@@;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 username and password to login into oracle database #
echo "Enter oracle username"
read username

echo "Enter oracle user password"
stty -echo
read password
stty echo

# Get the query, no validation applied for query #
echo "Enter the SQL query to execute"
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

Now login into oracle database and execute any query like below and check the output.

SQL> SELECT * FROM STUDENT;

STUDENT_ID STUD_NAME            DEPT                 SCHOLARSHIP
---------- -------------------- -------------------- -----------
      2001 AMIT KUMAR           CS                           800
      2002 SUMIT ARORA          CS                           950
      2003 ANKITA VERMA         CS                          1100
      2004 ROBERT               EC                          1250
      2005 SUDHEER MISHRA       EC                          1250
      2006 ANANYA KUMAR         EC                          1300
      2007 MAYANK VERMA         EC                          1500
      2008 SUDESH SHARMA        EC                          1600
      2009 RAVI KUMAR           IT                          2450
      2010 DINESH AGARWAL       IT                          2850
      2011 AMIT AGARWAL         IT                          2950
      2012 ANAMIKA SAIN         IT                          3000
      2013 DEVENDRA TRIPATHI    IT                          3000
      2014 ANIL KUMAR           IT                          5000

14 rows selected.

SQL>

$ ./exec_sql_query.sh
Enter oracle username
scott
Enter oracle user password 
Enter the SQL query to execute 
SELECT * FROM STUDENT;

      2001 AMIT KUMAR           CS                           800
      2002 SUMIT ARORA          CS                           950
      2003 ANKITA VERMA         CS                          1100
      2004 ROBERT               EC                          1250
      2005 SUDHEER MISHRA       EC                          1250
      2006 ANANYA KUMAR         EC                          1300
      2007 MAYANK VERMA         EC                          1500
      2008 SUDESH SHARMA        EC                          1600
      2009 RAVI KUMAR           IT                          2450
      2010 DINESH AGARWAL       IT                          2850
      2011 AMIT AGARWAL         IT                          2950
      2012 ANAMIKA SAIN         IT                          3000
      2013 DEVENDRA TRIPATHI    IT                          3000
      2014 ANIL KUMAR           IT                          5000

If you have any other method, write below in comment.