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.