≡ 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
#!/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

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

{ 7 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?

    Thanks,
    Oscar.

  • 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,

    Oscar.

  • 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

    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

  • Mani March 16, 2015, 8:05 pm

    Could you please let me know any suggestion for the below?

    its in ksh

    I am getting below error

    Code:
    SELECT ‘ALTER DISKGROUP ‘ $DG ‘ DROP FILE ”’||R.NAME||”'; ‘
    *
    ERROR at line 1:
    ORA-00911: invalid character

    Tried different combinations like ${DG}, $DG, “${DG}”.

    Function :-

    Code:
    function DELNWE5O {
    export ORACLE_HOME=/ora00/app/oracle/product/11.2.0/grid_1
    export ORACLE_SID=+ASM
    export PATH=${ORACLE_HOME}/bin:${PATH}
    DG=`sqlplus -s ‘/as sysasm’ < /ora00/grid/Mani/dg.log
    export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
    export ORACLE_SID=nwe5o
    export PATH=${ORACLE_HOME}/bin:${PATH}
    LIBPATH=${ORACLE_HOME}/lib
    sqlplus -s ‘/as sysdba’ <<-"EOF"
    SET PAGES 0 LINES 175 HEAD OFF FEED OFF
    spool /ora00/grid/Mani/delete_list_ods_nwe5o.sql
    SELECT 'ALTER DISKGROUP ${DG} DROP FILE '''||R.NAME||'''; '
    FROM DBA_REGISTERED_ARCHIVED_LOG R, DBA_CAPTURE C, V$ASM_FILE F
    WHERE R.CONSUMER_NAME = C.CAPTURE_NAME
    AND R.NEXT_SCN < C.REQUIRED_CHECKPOINT_SCN
    AND F.FILE_NUMBER = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,1)+1,INSTR(R.NAME,'.',1,2)-INSTR(R.NAME,'.',1,1)-1)
    AND F.INCARNATION = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,2)+1);
    spool off
    EXIT
    EOF
    }

    Thanks

Leave a Comment