Sql*plus always returns exit code 0?

Question

You have to explicitly tell sqlplus to do that, in your script. Basically, there are two statements that you can use:

• WHENEVER SQLERROR EXIT SQL.SQLCODE
• WHENEVER OSERROR EXIT

For example:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/

And for OS errors:

WHENEVER OSERROR EXIT FAILURE
START no_such_file

For more information, see this and that.

Hope it helps. Good Luck!

Vlad's is the answer I'd use. To augment his, however, I try to use an explicit EXIT statement if I really need that return status. For example

column status_for_exit new_value exitcode noprint
select status_computation (parm, parm) as status_for_exit from dual;

exit &exitcode;

The best action might a combination of the other ideas on this page and the ideas at

Help with SQLPLUS please? How to make SQLPLUS startup with DEFINE `OFF` initially?

Make a login.sql file, or edit the global one to have

WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE

inside it. Then, if the file doesn't exist, it will error out. If a line fails, it will error out.

However, keep in mind that as the docs say at : https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve052.htm#SQPUG135 that certain commands still will not error out as you might expect.

For security reasons, things have changed in Oracle 12.2 according to these blog articles.

login.sql will be silently ignored unless you set explicitly

• on Linux : ORACLE_PATH
• on Windows : SQLPATH

to the directory where login.sql can be found.