• Uncategorized

About sql : How-do-you-execute-SQL-from-within-a-bash-script

Question Detail

I have some SQL scripts that I’m trying to automate. In the past I have used SQL*Plus, and called the sqlplus binary manually, from a bash script.

However, I’m trying to figure out if there’s a way to connect to the DB, and call the script from inside of the bash script… so that I can insert date and make the queries run relative to a certain number of days in the past.

Question Answer

I’m slightly confused. You should be able to call sqlplus from within the bash script. This may be what you were doing with your first statement

Try Executing the following within your bash script:

echo Start Executing SQL commands
sqlplus <user>/<password> @file-with-sql-1.sql
sqlplus <user>/<password> @file-with-sql-2.sql

If you want to be able to pass data into your scripts you can do it via SQLPlus by passing arguments into the script:

Contents of file-with-sql-1.sql

 select * from users where username='&1';

Then change the bash script to call sqlplus passing in the value


sqlplus <user>/<password> @file-with-sql-1.sql $MY_USER

You can also use a “here document” to do the same thing:


sqlplus connectioninfo << HERE
start file1.sql
start file2.sql $VARIABLE

Maybe you can pipe SQL query to sqlplus. It works for mysql:

echo "SELECT * FROM table" | mysql --user=username database

Here is a simple way of running MySQL queries in the bash shell

mysql -u [database_username] -p [database_password] -D [database_name] -e "SELECT * FROM [table_name]"

I’ve used the jdbcsql project on Sourceforge.

On *nix systems, this will create a csv stream of results to standard out:

java -Djava.security.egd=file///dev/urandom -jar jdbcsql.jar -d oracledb_SID -h $host -p 1521 -U some_username -m oracle -P "$PW" -f excel -s "," "$1"

Note that adding the -Djava.security.egd=file///dev/urandom increases performance greatly

Windows commands are similar: see http://jdbcsql.sourceforge.net/

If you do not want to install sqlplus on your server/machine then the following command-line tool can be your friend. It is a simple Java application, only Java 8 that you need in order to you can execute this tool.

The tool can be used to run any SQL from the Linux bash or Windows command line.


java -jar sql-runner-0.2.0-with-dependencies.jar \
     -j jdbc:oracle:thin:@//oracle-db:1521/ORCLPDB1.localdomain \
     -U "SYS as SYSDBA" \
     -P Oradoc_db1 \
      "select 1 from dual"

Documentation is here.

You can download the binary file from here.

As Bash doesn’t have built in sql database connectivity… you will need to use some sort of third party tool.

You may also like...

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.