How to Execute SQL from Within a Bash Script

How do you execute SQL from within a bash script?

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:

#!/bin/bash          
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

#!/bin/bash

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

How to run SQL in shell script

#!/bin/ksh
variable1=$( 
echo "set feed off
set pages 0
select count(*) from table;
exit
"  | sqlplus -s username/password@oracle_instance
)
echo "found count = $variable1"

Execute sql queries from shell script

I'm using this

dbhost=localhost
dbport=5432
dbuser=user
dbpass=pass
dbname=test
export PGPASSWORD="$dbpass"
dbopts="-h $dbhost -p $dbport -U $dbuser -d $dbname"

Then run sql script from file

psql $dbopts < "$path_to_sql_script"

Or from query var

query="
SELECT 1;
...
"
psql $dbopts <<< "$query"

Also pgpass can be set in special file ~/.pgpass like this

echo "$dbhost:$dbport:$dbname:$dbname:$dbpass" > ~/.pgpass
chmod 600 ~/.pgpass

How to execute a .sql script from bash

You simply need to start mysql and feed it with the content of db.sql:

mysql -u user -p < db.sql

Executing sql in shell script

Problem is that once your 'sqlplus system/****' executes, it is entering sqlplus command prompt, next line 'select * from dual;' is not passed by bash to that sqplus application prompt, and it is executed by bash directly.

You probably need something along like these suggestions:

https://stackoverflow.com/a/10278103/2002557

specifically something like:

/home/oracle/db_env.sh:

sqlplus -s <<EOF system/*****@<host name>/<db name>
select * from dual;
select something from somwhere;
# etc.
EOF

Run sql query from If else block in shell script

Try removing the indentation inside of the here-document:

if float_cmp "$size1 > 7.50"; then
    echo "### THE DATA SIZE IS GREATER THAN 7.5 GB ###"
    echo "############### DROPPING CREATED $IMPUSER USER ###########################"
    "${PATH_TO_CLIENT}"sqlplus $EXPUSER/$EXPPWD@$ENDPOINT<< EOF
drop user $IMPUSER cascade;
exit;
EOF
    exit 1
else
    echo "### THE DATA SIZE IS OKAY ###"
fi

execute .sql file in bash with params

You need a combination of setting SQL parameters first and then loading your .sql file.

In your bash script you can use mysql with the -e option to execute an SQL statement. The same method must then be used to tell mysql to load the file instead of reading it using bash.

See this example script.

If you only want to execute a single statement, see this question.

---

Related Topics