Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 20 '13 at 12:27

This question came from our site for professional and enthusiast programmers.

    
What are you calling SQL*Plus from? A shell script? A C prog? –  Phil May 20 '13 at 14:03

1 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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.