In a shell script:
#!/bin/bash
sqlplus user/pass@server/DATABASE<<THEEND
-- Change "1" to the desired fatal return code
whenever sqlerror exit 1;
@yoursqlscript.sql
quit;
THEEND
Or you can just run:
sqlplus user/pass@server/DATABASE @yoursqlscript
... and put the whenever sqlerror exit 1; at the top of your .sql script(s).
This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer.
In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets about performing the task given to it by its parent.
When that child process completes, it returns a code to the parent (success or failure) - AFAICR, - 0 is success, failure is anything else.
So, the new bash process spawned by your shell (aka "the child") takes its variables from your original (i.e. the spawning or parent) bash shell. If you don't tell the new SQLPlus shell where to find the SQLPlus executable, then it will throw an error.
Unusually for Linux/Unix and Oracle, the error message is actually informative. This is not always the case! :-). If you want a good laugh about Unix, read this. In fairness to Linux/Unix, they've come a long way since that document was written, but it's still worth a read.
So, unless you "export" - i.e. feed the values of your environment to all of the child processes - your system will misbehave.
Take a look at Andrew Tanenbaum's book - or any good text on modern Unixen (or *nix). There's plenty of info out there - just Google Unix and processes - you'll get a truckload of stuff.
@StringerBell makes a good point. In one job I worked in, we had 4 test instances on the one machine. We'd log in and then go to our desired instance and run oraenv.blue, oraenv.red... There are pros and cons - it was easy enough to forget which instance one was on - it's maybe a good idea to change the prompt as well - to give DBAs a fighting chance :-)
Best Answer
If you us a shell to execute a command in backticks (
`), it is executed in a subshell, so all the environment variables it sets are only set in the subshell and are gone when the subshell exits.You have to "source" the script with
without the backticks.