title: How to run a SQL command within a Bash script and save the output of that command to a variable
body: <p>I am looking to enclose some Oracle components within a Bash script that will perform a set of goals:</p>
  
  <ol>
  <li>Log into a remote server (where my Oracle DB resides) as root.</li>
  <li>Performs an "su - oracle".</li>
  <li>Logs into sqlplus environment as a specific Oracle user.</li>
  <li>Performs an SQL select command and stores the output of that command into a variable.</li>
  <li>Displays the result of that variable in the Bash shell. </li>
  </ol>
  
  <p>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:</p>
  
  <pre><code>#!/bin/sh
  ssh -q root@5.6.7.8
  sqlplus ABC/XYZ@core &lt;&lt;ENDOFSQL
  select CREATE_DATE from PREPAID_SUBSCRIBER where MSISDN='12345678912';
  exit;
  ENDOFSQL
  </code></pre>
  
  <p>Instead, here is how I tried to set this up: </p>
  
  <pre><code>#!/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`
  </code></pre>
  
  <p>Ideally, the datasource_name variable should now either take on values:</p>
  
  <pre><code>no rows selected
  </code></pre>
  
  <p>Or if there is an entry within the table:</p>
  
  <pre><code>CREATE_DATE
  -------------------
  07-06-2009 18:04:48
  </code></pre>
  
  <p>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.</p>
  
  <p>However, when I run that command, and do an: </p>
  
  <pre><code>echo "${datasource_name}"
  </code></pre>
  
  <p>I get...</p>
  
  <p>Warning: no access to tty (Bad file descriptor).
  Thus no job control in this shell.</p>
  
  <p>...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.</p>
  
  <p>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:</p>
  
  <pre><code>ssh root@5.6.7.8 "su - oracle"
  Warning: no access to tty (Bad file descriptor).
  Thus no job control in this shell.
  </code></pre>
  
  <p>If I do the following, I actually get into the sqlplus environment successfully with no issues:</p>
  
  <pre><code>ssh -q root@5.6.7.8 "su - oracle -c 'sqlplus ABC/XYZ@core'"
  </code></pre>
  
  <blockquote>
    <p>SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 29 12:35:06 2012</p>
    
    <p>Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.</p>
    
    <p>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</p>
    
    <p>SQL></p>
  </blockquote>
  
  <p>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. </p>