Databases
StackOverflow2013
Postsdbo
POHow to run a SQL command within a Bash script and save the output of that command to a variable
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to run a SQL command within a Bash script and save the output of that command to a variable
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USArun Reddy
    1. USArun Reddy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to run a SQL command within a Bash script and save the output of that command to a variable
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to run a SQL command within a Bash script and save the output of that command to a variable
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to run a SQL command within a Bash script and save the output of that command to a variable
      1. USjohnshen64
      1. VTFavorite
    1. COWhy do you ssh as the root (!) user, only to then su to the oracle users? Why not ssh directly as the oracle user?
      singulars
      1. POHow to run a SQL command within a Bash script and save the output of that command to a variable
      1. UStheglauber
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload