Controlling Oracle SQL*Plus from shell scripts


	Main Page

Main page

	About Me

Software I wrote Resume Friends of mine Pictures Musicianship Stuff I have for sale

	Personal News

2010: March, April. 2009: January, March, August. 2008: Jan, Feb, Apr, May, July, August, September, October. 2007: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2006: Jan, Feb, Mar, Apr, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2005: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2004: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2003: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2002: Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2001: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. 2000: Jan, Feb, Apr, May, Jun, Jul, Aug, Oct, Nov, Dec. 1999: Jan, Feb, Jun, Oct, Dec. 1998: Jul, Aug, Sep, Nov.

	Geek Stuff (computer related)

Digital Music Java Why LiveWire Sucks Why ASP Sucks (a bit) Linux MacOS Unix Oracle Perl Emacs O'Reilly

	(some of) My Interests

Humor Sony Playstation Cars

	Search



	Ads

I have a bunch of Oracle SQL scripts that I'm using for a project I'm working on. They were all designed to be used interactively to create users, blow away and recreate the schema, and back-up and restore the data itself. I wanted to script a bunch of them together to do stuff like:

Blow away the schema, recreate the schema, and load test data
Blow away the development database and replace it with a copy of the production database

As it happens, Oracle's SQL*Plus interactive command-line SQL tool can be configured to behave like a batch program, supressing most of the user feedback and exiting in case something goes wrong and returning an error code to the calling script. However in order to do this one has to feed it some commands that aren't appropriate during interactive use. So I needed a way to wrap those commands around the interactive SQL scripts I had already written, while leaving the scripts on disk alone so I could continue to use them interactively. What I needed was the ability to pipe the output from several commands into a single invocation of SQL*Plus. The steps SQL*Plus should see are:

Stop on errors
Run an interactive script called foo.sql
Exit

There was plenty of documentation hinting that this was possible but examples were scarce so I thought I'd post this example. All three examples below work equivalently.

#!/bin/bash

cat <<EOF | sqlplus -S userid/password
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
@foo
exit
EOF

(cat <<EOF
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
EOF
cat foo.sql
cat << EOF
EXIT
EOF
) | sqlplus -S userid/password

(cat <<EOF ; cat foo.sql ; cat << EOF ) | sqlplus -S userid/password
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
EOF
EXIT
EOF