≡ Menu

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

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
Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

{ 10 comments… add one }

  • Daniel Reimann January 29, 2010, 11:24 am

    Thank you for posting these examples.

  • Amr El-Sharnoby January 29, 2010, 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

  • Mark January 29, 2010, 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}
    
  • Shantanu Oak January 30, 2010, 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)

  • Juan Giordana January 31, 2010, 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.

  • nixCraft January 31, 2010, 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.

  • ramu May 23, 2011, 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 ?

  • Van June 15, 2013, 7:28 am

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

  • Ashant October 23, 2013, 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
  • Ashant October 23, 2013, 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
    

Leave a Comment