How to run a SQL command within a Bash script and save the output of that command to a variable

Question

I am looking to enclose some Oracle components within a Bash script that will perform a set of goals:

1. Log into a remote server (where my Oracle DB resides) as root.
2. Performs an "su - oracle".
3. Logs into sqlplus environment as a specific Oracle user.
4. Performs an SQL select command and stores the output of that command into a variable.
5. Displays the result of that variable in the Bash shell.

I have looked through a couple examples here on stackoverflow, many of which seem to go over executing a command but not necessarily detailing how to display the output to the user (although I am still examining a few more). For example, assuming all key exchanges are setup beforehand, a method could be to use the following:

#!/bin/sh
ssh -q root@5.6.7.8
sqlplus ABC/XYZ@core <<ENDOFSQL
select CREATE_DATE from PREPAID_SUBSCRIBER where MSISDN='12345678912';
exit;
ENDOFSQL

Instead, here is how I tried to set this up:

#!/bin/sh
datasource_name=`echo "select CREATE_DATE from PREPAID_SUBSCRIBER where MSISDN='12345678912';" | ssh -q 5.6.7.8 "su - oracle -c 'sqlplus -S ABC/XYZ@core'" | tail -2 | head -1`

Ideally, the datasource_name variable should now either take on values:

no rows selected

Or if there is an entry within the table:

CREATE_DATE
-------------------
07-06-2009 18:04:48

The tail and head commands are to get rid of the empty lines in the output, and the ssh -q and sqlplus -S options are for ignoring warnings.

However, when I run that command, and do an:

echo "${datasource_name}"

I get...

Warning: no access to tty (Bad file descriptor). Thus no job control in this shell.

...instead of one of the two outputs above. If I understand correctly, this is a warning that can be caused depending on whether a specific shell is used, but most online sources indicate that this can be ignored. The nice thing about this warning is that it appears my command above is actually running and storing "something" into datasource_name, but it just isn't what I want.

Now to simplify this problem, I noticed I get the same tty warning when I simply try to su to oracle on the remote machine from the box where the bash script runs:

ssh root@5.6.7.8 "su - oracle"
Warning: no access to tty (Bad file descriptor).
Thus no job control in this shell.

If I do the following, I actually get into the sqlplus environment successfully with no issues:

ssh -q root@5.6.7.8 "su - oracle -c 'sqlplus ABC/XYZ@core'"

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 29 12:35:06 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

SQL>

If I understand why the problem above is occurring, it is possible that I can figure out how to get my script to work properly. Any suggestions would be greatly appreciated! Thank you.

Answer 1

change the first line to:

ssh -t  root@5.6.7.8 "su - oracle"     

to get a tty to see if that would work for you.

another thing you can do in your script is to redirect stderr to your variable as well if you would like to see that as well in your variable, which does not appear to be the case for you, though I have done so in the past in some cases. There is an example in the comments below.

Answer 2

This is a sample script for MySQL, but it can be easily edited for Oracle :

#!/bin/bash

remote=oracle@5.6.7.8

ssh -q -t $remote <<EOF
bash <<EOFBASH
mysql <<ENDOFSQL>/tmp/out
show databases;
ENDOFSQL
EOFBASH
EOF

scp $remote:/tmp/out /tmp/out
ds=$(</tmp/out)

cat <<EOF
START OUTPUT
$ds
END OUTPUT
EOF

rm /tmp/out

Tested, works well. Instead of using su - oracle, try to ssh directly to oracle user ;)