HowTo: Use Oracle / MySQL SQL Commands In UNIX Shell Scripts

by on January 29, 2010 · 10 comments· LAST UPDATED January 29, 2010

in

How do I call Oracle or MySQL sql statements in UNIX / Linux shell scripts?

You need to use a here document feature supported by sh / bash or ksh. The syntax is as follows:

MySQL: Use SQL Directly In Shell Scripts

#!/bin/sh
user="dbuser"
pass="dbpassword"
db="dbnme"
mysql -u "$user" -p"$pass" "$db" <<EOF
  sql-statement-1;
  sql-statement-2;
EOF

Using Shell Variables In SQL

#!/bin/sh
user="dbuser"
pass="dbpassword"
db="dbnme"
sql="select * from tal_name"
mysql -u "$user" -p"$pass"  <<EOF
  use $db;
  $sql;
EOF

Oracle: Use SQL Directly In Shell Scripts

#!/bin/sh
user="system"
pass="manager"
sqlplus -S $user/$pass <<EOF
  sql-statement-1;
  sql-statement-2;
 exit;
EOF

Using Shell Variables In SQL

#!/bin/sh
user="system"
pass="manager"
var="$1"
sqlplus -S $user/$pass <<EOF
 SELECT * FROM tableName WHERE username=$var;
 exit;
EOF

You can call sql statements from .sql file itself as follows:

#!/bin/ksh
sqlplus -S system/manager @my_sql_script.sql
TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 10 comments… read them below or add one }

1 Daniel Reimann January 29, 2010 at 11:24 am

Thank you for posting these examples.

Reply

2 Amr El-Sharnoby January 29, 2010 at 11:31 am

Hello,
For MySQL , I think you can also ;
1- pipe it, for example;
echo "sql-statement;" | mysql -u USER -p PASSWORD
2- Or, use “-e” option to mysql command, for example;
mysql -u USER -p PASSWORD -e "sql-statement;"

Thanks

Reply

3 Mark January 29, 2010 at 4:36 pm

I like the HERE approach, but I prefer to keep my passwords out of the process list and use the expect command to do so. The script below would need some tweaking (make it a function) to test and the end result, but it shows the way I use expect.

#!/bin/bash
script=/some/path/expect-ssh.sh
chmod 0700 ${script}
"cat" > ${script} <<EOF
#!/usr/bin/expect --
set timeout 15
spawn -noecho /usr/bin/ssh ${ssh_user}@${ssh_host}
expect {
  "(yes/no)?" { exp_send "yes\r"; exp_continue }
  "ssword:"   { exp_send -- "${ssh_passwd}\r" }
  timeout     { abort }
  connected
}
interact
EOF
# call
${script}

Reply

4 Shantanu Oak January 30, 2010 at 11:26 am

My 2 cents:
1) You can save the standard out like this…

mysql -u "$user" -p"$pass" < totest.txt

2) Do not forget mysql can show the output in html or xml format…

mysql --xml -u "$user" -p"$pass" < totest.txt

(added the code tag)

Reply

5 Juan Giordana January 31, 2010 at 5:28 pm

The problems you’ll have to deal when using the MySQL client inside a shell script appears when you want to do things in the middle. For example, a backup script would have this flow:

– Lock all tables/databases.
– Perform the backup.
– Unlock (release) all tables/databases.

Inside a shell script, you execute the first step, the MySQL client then finish the query and exits, releasing all the locks you just added.

In this cases is better to use P* languages (PHP/Python/Perl) instead, since they only connect once, and disconnects whenever you want or when the program ends.

Reply

6 nixCraft January 31, 2010 at 6:37 pm

@Juan,

For backup you can use LVM2 snapshots, take a look at the following
http://www.lenzg.net/mylvmbackup/

However, I agree with using P* languages.

Reply

7 ramu May 23, 2011 at 10:37 am

how to assign oracle sql select statement result to unix variable ?
ex :select ename from emp where empno=7789;
can any give answer for this ?

Reply

8 Van June 15, 2013 at 7:28 am

thanks for tip. I will try to use it inside my script.

Reply

9 Ashant October 23, 2013 at 8:59 am

working for verification of the user id and corresponding byte in oracle bash script

#!/bin/bash
if [ "$(id -un)" = "root" ]; then
su  oracle -c "source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh &&  /u01/app/oracle/product/11.2.0/xe/bin/sqlplus fido/Fido_1@xe  < /u01/app/oracle/product/11.2.0/xe/uid.log 2>&1
        desc activities;
EOF"
cat /u01/app/oracle/product/11.2.0/xe/uid.log  |  grep -i USER_ID | grep 128   && echo "TEST CASE PASS"
    if [ $? -ne 0 ] ; then
        echo "TEST CASE FAIL"
    fi
else
            echo "Please execute MFAS installer as root user"
            exit 1
fi

Reply

10 Ashant October 23, 2013 at 9:54 am

how to execute or run oracle bash script without moving to sql prompt
Above code with inline comments

#!/bin/bash
#verify that login by root user
if [ "$(id -un)" = "root" ]; then
#execute the following commands to set $ORACLE_HOME and $ORACLE_SID And after successful execution of the oracle_env.sh login by bandu as user having using password as  bandupassword at "xe" as oracle sid.
su  oracle -c "source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh &&  /u01/app/oracle/product/11.2.0/xe/bin/sqlplus bandu/bandupassword@xe  < /u01/app/oracle/product/11.2.0/xe/uid.log 2>&1
        desc activities;
EOF"
cat /u01/app/oracle/product/11.2.0/xe/uid.log  |  grep -i USER_ID | grep 128   && echo "TEST CASE PASS"
    if [ $? -ne 0 ] ; then
        echo "TEST CASE FAIL"
    fi
#if user is not login by root user exit
else
            echo "Please execute script as root user"
            exit 1
fi

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: