How do I read sql query into shell script?

Hello All,

I'm trying to put together a shell script that will:

1. connect to an oracle database
2. execute a query
3. save the output to a csv file

I know that I can execute the sqlplus -s user/pass @dbsid and get logged in. What I would like to do is have my query in a separate text file and read that file into the shell script to query the database. How do I do this?

Something like:

#!/bin/bash
sqlplus -s user/pass@oracle_sid
query=`cat file_with_query`

I could also use the <<EOF/EOF and pass the queries this way?:

#!/bin/bash
sqlplus -s user/pass@oracle_sid <<EOF
      {enter my query here}        
      exit;
EOF

Try redirecting the query into it, sqlplus ... < file

To insert your SQL, you could set up a variable and use that.

Could I suggest that you move you credentials from the sqlplus command line more like this:-

#!/bin/bash

while read line
do
   my_sql="$my_sql\n$line"
done < file

sqlplus -s <<-EOSQL
   $user/$pass@$sid
   $my_sql
EOSQL

If you choose not to, then whilst your sqlplus process is running, anyone could execute a ps command and see your database credentials.

I hope that this helps,
Robin

Another couple of ways:

#!/bin/bash
sqlplus -s user/pass@oracle_sid @file.sql

(note that file.sql will need to contain an EXIT, or it will just hang)

#!/bin/bash
sqlplus -s user/pass@oracle_sid <<SQL
   @file.sql
   EXIT
SQL

You are still hanging out your credentials for anyone to see with a simple ps though.....

This is good stuff guys. Thanks!

I don't mind having the user/pass shown as the user I'm using have strictly a read-only access but I get what you're saying (it's good practice not to have passwords in the clear).

So the code will look like:

#!/bin/bash
user=sqluser
pass=sqlpass
while read line
do
   my_sql="$my_sql\n$line"
done < file

sqlplus -s <<-EOSQL
   $user/$pass@$sid
   $my_sql
EOSQL

And execute it using:

./mysqlqueryscript > results.csv

?

Looks fine to me, except you haven't defined $sid

Personally, I would still keep credentials hidden. If, at a later stage, you get read/write or indeed you may every use the account to read something sensitive to either the company, customers or under any data protection laws, then you can imagine that letting them spread now could be problematic later, especially if you want to prove who did what later.

Maybe it's just my paranoia working for a financial company looking after the data of millions of real people and being bound up in UK laws to (quite rightly) protect them. :o

Robin

You can always use /nolog, with CONNECT inside the SQL (or SQL file, or login.sql).

Ok. So how do I get the password into the script? I wanted to automate the script and needed some way to pass the creds into the script.

If the script is readable, then others will be able to either see the password or deduce where it's held and read it from there. You could use the tool shc (search this site for lots of thread about it) to obscure your script or you could defined the database use a trusting the OS. This will only work without specifying a SID though:-

SQL> alter user username identified externally;

Then you can change your code to be:-

sqlplus / <<-EOSQL
:
:
etc.

Would this suffice?