≡ Menu

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
# Validate the value of ORACLE_HOME #
# If ORACLE_HOME is empty #
if [ -z $ORACLE_HOME ]
        echo "Set the ORACLE_HOME variable"
        exit 1
# If ORACLE_HOME doesn't exist #
if [ ! -d $ORACLE_HOME ]
        echo "The ORACLE_HOME $ORACLE_HOME does not exist"
        exit 1
# Validate the value of ORACLE_SID #
if [ -z $ORACLE_SID ]
        echo "Set the ORACLE_SID variable"
        exit 1
sid_dir=`echo $ORACLE_HOME | sed -n 's@^\(\/[^\/]\+\/\).*$@\1@;p'`
# Check the given ORACLE_SID is valid.
if [ ! -d $sid_dir/oradata/$ORACLE_SID ]
        echo "The ORACLE_SID is invalid"
        exit 1

# 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
        exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
                echo $output

In database,

SQL> select * from test;

 $ ./sql_query.sh
Enter the username
Enter password
Enter the query
select SNO from test;

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

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 6 comments… add one }

  • MM February 18, 2012, 6:16 am

    This is not working on AIX…

  • Chinmoy October 17, 2012, 10:29 pm

    This is not working ….showing the ORACLE_SID is invalid

  • Oscar January 16, 2013, 10:10 am

    I guess (sorry if this seems to be too obvious) we need to have Oracle_home, meaning have Oracle in your Unix/AIX/Linux environment.
    Can you please elaborate a little bit on this with some advice on this regards?


  • Oscar January 16, 2013, 10:13 am

    Also, would you guide me on “How to” extract informatino from Cron itself?
    I need to get the data to know exactly which processes are schedule to run in a particular day and/or which scripts are already run *(from Cron) at a given time of the day and which ones are pending for that same day.

    Appreciate your kind support,


  • Mark Stewart October 6, 2013, 5:24 pm

    There is a big security with this script; it exposes the user’s Oracle ID and password to other users that might invoke the ps -ef command while the query is running. Although many queries are quick, some are not. You can fix the above example by replacing the “-s $username/$password ” parm with “/nolog” and adding the following line right after the set statement:
    connect $username/$password

  • Anil Babu Samineni October 21, 2013, 6:40 am


    This is good
    But some OS it is not working

    Present Now I am using HP Server but host is not working through a server

Leave a Comment