I am calling sqlplus from within a bash script, providing some arguments but there may potentially exist a situation where some of the arguments are not correct, for example the user name. In that case sqlplus reports an error that the user name or password is invalid and prompts the user to enter a user name. That prompt is not desired. I would want sqlplus to just report the error on stderr, bail out and exit with an error code. To perform completely non-interactively.
For example, the bash script calls out sqlplus like this:
sqlplus $username/$password@ORCLPDB1 @script.sql
If the $username variable is incorrect (like such user does not exist), it reports the error and keeps waiting for a new user name for the user to input to stdin. Does sqlplus have any feature to immediately quit on error, without prompting anything? If not, are there any simple and straightforward workarounds?
Looks like if sqlplus gets all of its commands piped to it into its standard input, it actually doesn't prompt the user for anything, no matter if the -S flag is provided or not. Also in this case the @script.sql parameter seems ignored (it doesn't get executed if given as a command line argument). So, to execute a script so that there are no prompts and errors make sqlplus actually exit with an error code, you have to call sqlplus like that:
sqlplus /nolog <<EOF
whenever sqlerror exit sql.sqlcode
connect $username/$password @ORCLPDB1
@script.sql
EOF
The $? variable is populated on error but the error code itself doesn't make much sense to me. It is not referring to the number in the ORA-XXXXX scheme.