Search code examples
sqloracle-databaseshellsqlplus

How to pass unix variable in where condition of query?

I have a file whose filename I am storing in a shell variable and I wish to pass that variable in the WHERE condition of my SQL select query. How can I achieve this ?

my code

cd /path/to/folder
var =$(ls tail)
id_var=$(echo "$var" | cut -f 1 -d '.')
...
...
sqlplus -s user/pwd@db < mysql.sql > output.txt

cat mysql.sql

select * from Records where "GlobalId"='$id_var'
Solution

  • From this answer:

    cd /path/to/folder
var =$(ls tail)
id_var=$(echo "$var" | cut -f 1 -d '.')
sqlplus -s user/pwd@db @mysql.sql "${id_var}" > output.txt

    Then in mysql.sql use &1 to substitute the first start argument:

    select * from Records where "GlobalId"='&1'

    Note: &1 is a substitution variable (and not a bind variable) so you will need to make sure that the value passed in does not perform any SQL injection attacks.