The WHENEVER SQLERROR command tells SQL*Plus what to do when a SQL statement or PL/SQL block fails to execute properly. The simplest way to use it is to issue the following command, which tells SQL*Plus to simply abort the script when an error occurs.

WHENEVER SQLERROR EXIT

Now, when a SQL error occurs, SQL*Plus will exit, as the following example shows:

SQL> WHENEVER SQLERROR EXIT 
SQL> SELECT kristi FROM dual; 
SELECT kristi FROM dual 
       * 
ERROR at line 1: 
ORA-00904: invalid column name 


Disconnected from Oracle7 Server Release 7.3.3.0.0 - Production Release 
PL/SQL Release 2.3.3.0.0 - Production 
$

When SQL*Plus exits like this, the default behavior is to commit any transaction that might be open. For a simple SELECT statement like that shown in the previous example, this is not a problem. When you are changing records, it might be. If your script executes several SQL statements that change data, you may not want to commit unless all the changes can be made. In this situation, use the ROLLBACK option to tell SQL*Plus to roll back when an error occurs, like this:

WHENEVER SQLERROR EXIT ROLLBACK

If you’re calling SQL*Plus from a Unix shell script, DOS batch file, VMS command file, or the equivalent, you can have it pass back a return code so your shell script can tell whether your script executed successfully or not. The following command tells SQL*Plus to pass back a standard failure code when an error occurs:

WHENEVER SQLERROR EXIT FAILURE ROLLBACK

The precise code that gets passed back will vary from one operating system to the next. If a simple success/fail indication is not enough, you can have SQL*Plus pass back the specific Oracle error code or any other value you want. The following example shows how to pass back the Oracle error code when a SQL error occurs:

WHENEVER SQLERROR EXIT SQL.SQLCODE

You could also choose to return any arbitrary number, the value of a numeric bind variable, or the value of a substitution variable.

The WHENEVER SQLERROR EXIT command will catch any errors in a PL/SQL block, but only if those errors are raised back to the SQL*Plus level. PL/SQL has its own error-handling mechanism, and using it can prevent SQL*Plus from knowing that an error ever occurred.

The following PL/SQL block does not contain an error handler, so any SQL errors will be implicitly raised to the calling routine, which in this case is SQL*Plus:

BEGIN
  UPDATE employee 
     SET employee_billing_rate = employee_billing_rate * 1.10;
  COMMIT;
END;
/

However, you could rewrite the block so that it includes an error handler. In that case, the PL/SQL error handler would get the error, and SQL*Plus would not know about it. Here is the same block, but with an error handler:

DECLARE
  success_flag BOOLEAN;
BEGIN
  BEGIN
    UPDATE employee 
       SET employee_billing_rate = employee_billing_rate * 1.10;
    success_flag := TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      success_flag := false;
  END;
  
  IF success_flag THEN
    COMMIT;
  ELSE
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('The UPDATE failed.'),
  END IF;
END;
/

In this example, the UPDATE statement is contained in its own PL/SQL block, and any error related to that statement will be trapped by the exception handler for that block. Even if an error occurs, as far as SQL*Plus is concerned, this block will have executed successfully. If you want to handle an error within PL/SQL, but still abort the SQL*Plus script, you can use the RAISE_APPLICATION_ERROR procedure. This procedure is part of a PL/SQL package named DBMS_STANDARD, and should exist in all installations. You call it like this:

RAISE_APPLICATION_ERROR (error_code, error_message);

where:

When you call RAISE_APPLICATION_ERROR from a PL/SQL block, control immediately returns to the calling block. You must call the procedure from the outermost PL/SQL block in order to return the error to SQL*Plus. When that happens, SQL*Plus will print the error message and then take whatever action you specified in the most recent WHENEVER SQLERROR command. The next PL/SQL block is the same as the previous one, except for the addition of the RAISE_APPLICATION_ERROR command, which is used to notify SQL*Plus of an error:

DECLARE
  success_flag BOOLEAN;
BEGIN
  BEGIN
    UPDATE employee 
       SET employee_billing_rate = employee_billing_rate * 1.10;
    success_flag := TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      success_flag := false;
  END;
  
  IF success_flag THEN
    COMMIT;
  ELSE
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('The UPDATE failed.'),
    RAISE_APPLICATION_ERROR (-20000,
   'The UPDATE of employee billing rates failed.'),
  END IF;
END;
/

Now if an error occurs, SQL*Plus will know about it and can abort the script.

The WHENEVER OSERROR command tells SQL*Plus what to do when an operating system error occurs. Running out of disk space would be a likely operating-system error, one that you might encounter when spooling large amounts of output from a SQL query.

WHENEVER OSERROR works pretty much the same as the WHENEVER SQLERROR command. The simple version, which just causes SQL*Plus to exit when an error occurs, looks like this:

WHENEVER OSERROR EXIT

By default, any changes are committed when SQL*Plus exits. You can change that behavior using the ROLLBACK keyword as follows:

WHENEVER OSERROR EXIT ROLLBACK

As with WHENEVER SQLERROR, you can pass a return code back to a shell script in order to allow it to detect the error. For example:

WHENEVER OSERROR EXIT FAILURE

Unlike the SQLERROR version of the command, there is no equivalent to SQL.SQLCODE for operating-system errors. The other options still apply, however, and you can return an arbitrary value, the value from a bind variable, or the value of a substitution variable.

The following example archives any project_hours data over one year old to a file, then deletes the data that was archived. In order to be sure no data is deleted without first being successfully archived, the WHENEVER commands are used to abort the script in the event of an error.

WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE

--Turn off page headings, column titles, etc.
SET PAGESIZE 0
SET HEADING OFF
SET COLSEP ","

--Archive old records
SPOOL project_hours_archive.txt
SELECT * 
  FROM project_hours
 WHERE TRUNC(time_log_date) <= ADD_MONTHS(TRUNC(SYSDATE),-12);
SPOOL OFF

--Delete the records just archived
DELETE 
  FROM project_hours
 WHERE TRUNC(time_log_date) <= ADD_MONTHS(TRUNC(SYSDATE),-12);