Appending data from one program to the other using batch commands

Question

I have a database which sometimes crashes. I want to create a batch program that does this:

1. Runs SQL*Plus.
2. Makes SQL*Plus run a script from a plain text file.
3. Gets the results back to the main batch program.
4. Uses it to check if database is running or not.

I am having trouble in getting SQL*Plus to run something directly through the batch and getting the data back. Hopefully it's a simple process I don't know about.

Answer 1

Here's an example of how you can do this in shell script (bash or ksh for instance). Here's simple SQL script to test, it turns off (most?) of the usually undesired output from SQL*Plus:

set feedback off
set echo off
set heading off
set pages 0
whenever sqlerror exit 4
whenever oserror exit 8
select status from v$instance;
exit

You can run it like this, assuming your environment is set up properly (-s suppresses the banners and prompts):

result=$(sqlplus -s user/pass @script.sql)
if [ $? -eq 0 ] ; then
  # the script worked
  echo "$result"
else
  # something went wrong
  ...
fi

If you want to process multiple lines, you can use a while/read loop, with process substitution. Something like:

while read -r line ; do
   echo "Got: $line"
done < <(sqlplus -s user/pass @script.sql)

Or you can just redirect the output of SQL*Plus to a text file and process that later if it's not convenient in a variable.

On Windows, with cmd.exe scripts, it's all a bit harder, but you can use variants of what is described in this question: Setting a variable from an executable. Something like (not tested):

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do set RESULT=%%a

or

for /f "tokens=*" %%a in ('sqlplus -s user/pass @script.sql') do (
  @rem process one line at a time
  echo "%%a"
)

But you might be better off using PowerShell these days. (Which I know nothing about, but a quick google seems to indicate that there are good options for this.)