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

Posted on in Categories last updated 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

10 comment

  1. 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

  2. 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}
    
  3. 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)

  4. 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.

  5. 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 ?

  6. 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 [email protected]  < /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
  7. 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 [email protected]  < /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