Unix Shell Script to Execute Oracle SQL Query

by Sasikala on July 30, 2010

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

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

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… read them below or add one }

1 MM February 18, 2012 at 6:16 am

This is not working on AIX…

2 Chinmoy October 17, 2012 at 10:29 pm

This is not working ….showing the ORACLE_SID is invalid

3 Oscar January 16, 2013 at 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?

Thanks,
Oscar.

4 Oscar January 16, 2013 at 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,

Oscar.

5 Mark Stewart October 6, 2013 at 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

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

Hi,

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

Previous post:

Next post: