CODEWITHSUNDEEP
oracle-databasescriptingsqlplusexit-code
Tulains Córdova
Tulains Córdova

Reputation: 2639

Sql*plus always returns exit code 0?

Whenever I run a sql script using Sql*plus and check for $?, I get 0 even when the script wasn't succesful.

Example

#$ sqlplus user/password@instance @script.sql


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 7 14:20:44 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

     v$dataf-ile d,
            *
ERROR at line 6:
ORA-00933: SQL command not properly ended


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
$ echo $?
0
$

I would like it to return a non-zero value when an error occurs.

How can I achieve that ?

Upvotes: 36

Views: 46274

Answers (4)

Httqm
Httqm

Reputation: 839

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.

Upvotes: 1

Karl Henselin
Karl Henselin

Reputation: 1034

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.

Upvotes: 6

user405725
user405725

Reputation:

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!

Upvotes: 40

Andrew Wolfe
Andrew Wolfe

Reputation: 2096

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;

Upvotes: 7

Related Questions