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

by nixCraft on January 29, 2010 · 7 comments· last updated at January 29, 2010

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

You should follow me on twitter here or grab rss feed to keep track of new changes.

Featured Articles:

{ 7 comments… read them below or add one }

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

Thank you for posting these examples.

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

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}

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)

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.

6 Vivek Gite January 31, 2010 at 6:37 pm