Error Handling

SQL*Plus doesn't offer too much in the way of error handling. By default, SQL*Plus simply ignores errors and goes on to execute the next command you type in or the next command in the script you are running. For interactive use, this is good enough. If an error occurs, you will see the message and take appropriate action. However, the situation is different when you are running a script. Depending on what the script is doing, you may not want SQL*Plus to blindly proceed to the next command when an error occurs. Consider the following script, which creates a new table, copies data to it, then deletes the original table:

CREATE TABLE employee_copy AS SELECT * FROM employee; DROP TABLE employee;

If the CREATE TABLE command failed, you certainly wouldn't want the script to continue because you would lose all your data. To help with this type of situation, SQL*Plus provides the WHENEVER command.

11.5.1 The WHENEVER Command

With the WHENEVER command, you can give SQL*Plus instructions on what to do when an error occurs. Your choices are limited: You can continue when an error occurs or exit SQL*Plus entirely, possibly returning an error code. Returning an error code is useful if you are calling SQL*Plus from a Unix shell script or a DOS batch file.

You can handle two types of errors with WHENEVER. Each has its own variation of the command.

WHENEVER SQLERROR

Used to handle SQL errors and errors raised from PL/SQL blocks

WHENEVER OSERROR

Used to handle operating system errors, such as those you might get when you run out of disk space while spooling a large data extract

You cannot detect an error involving a SQL*Plus command. An example would be if you were to misspell a command, such as COLUMN. If your script contained the following command,

COLUM employee_name HEADEEN 'Employee Name' FLOORMAT A40

SQL*Plus would generate an error and continue on with the script as if nothing had happened . This isn't usually much of a problem. You should test your scripts to be sure your SQL*Plus commands are correct, which is easy to do. The consequences of a failed SQL*Plus command are usually no worse than some messy formatting of the output. SQL statements, on the other hand, can fail for various reasons that don't involve simple misspellings. A simple database change can cause a SQL statement that worked one day to fail the next. Similarly, with operating system errors, you don't know in advance, for example, when you will run out of disk space.

11.5.1.1 WHENEVER SQLERROR

The WHENEVER SQLERROR command tells SQL*Plus what to do when a SQL statement or PL/SQL block fails to execute properly. To use it, issue the command as shown in the following example, telling SQL*Plus to abort the script when an error occurs:

SQL> WHENEVER SQLERROR EXIT SQL> SELECT emp_id FROM dual; SELECT emp_id 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 SELECT statement as 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 an equivalent, you can have it pass back a return code so your shell script can tell whether your script executed successfully. 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 varies 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 choose to return any arbitrary number, the value of a numeric bind variable, or the value of a substitution variable.

11.5.1.2 Capturing SQL*Plus return codes

You can capture the error code returned by SQL*Plus when a script fails. This is handy when writing shell scripts because you can have a shell script take different courses of action depending on whether a SQL*Plus script succeeds. Example 11-30 shows a script that is guaranteed to fail. The script attempts to create a table but uses invalid syntax. The WHENEVER SQLERROR command ensures that the script then exits with a failure status.

Example 11-30. A script demonstrating WHENEVER's error handling

WHENEVER SQLERROR EXIT FAILURE CREATE TABLE pay_raises WITH COLUMNS ( employee_id NUMBER, raise NUMBER ); EXIT

On Unix and Linux systems, you can capture the status of the most recently executed command through the $? shell variable:

oracle@gennick02:~/sqlplus/ExampleScripts> sqlplus -s gennick/bramell @ex11-30 CREATE TABLE pay_raises WITH COLUMNS ( * ERROR at line 1: ORA-00922: missing or invalid option oracle@gennick02:~/sqlplus/ExampleScripts> echo $? 1

The exact values that SQL*Plus returns on success and failure depend upon your operating system. On Unix and Linux systems, SQL*Plus typically returns 0 for success and 1 for failure. The preceding run of SQL*Plus returned a 1, indicating an error.

Example 11-31 shows a script that traps an error from a SQL*Plus script. SQL*Plus is invoked from the if shell command. That invocation of SQL*Plus, in turn , invokes the script in ex11-30.sql . The if statement treats 0 as true and any other value as false. A successful execution of ex11-30.sql is followed by an execution of ex11-31_insert_raises . Otherwise, an error message is displayed through the standard output device.

Example 11-31. Capturing the SQL*Plus return status from Unix

#!/bin/bash if sqlplus -s gennick/secret @ex11-30 then sqlplus gennick/secret @ex11-31_insert_raises else echo Unable to create raise table. fi

The following is a run showing how Example 11-31 successfully captures and then acts upon the failure status from Example 11-30:

oracle@gennick02:~/sqlplus/ExampleScripts> . ex11-31.sh CREATE TABLE pay_raises WITH COLUMNS ( * ERROR at line 1: ORA-00922: missing or invalid option Unable to create raise table.

Be aware that the range of return codes you can pass back from SQL*Plus varies from one operating system to the next. Under Unix, return codes are limited to one byte, giving you a range of 0 to 255 to work with. Any failure codes in excess of 255 are returned modulo 256, which means that 256 is returned as 0, 257 as 1, and so forth. VMS, on the other hand, allows much larger values to be returned. Keep this in mind if you are writing a script that needs to be portable across different operating systems.

11.5.1.3 PL/SQL errors and WHENEVER

The WHENEVER SQLERROR EXIT command catches 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 occurred.

The PL/SQL block in Example 11-32 doesn't contain an error handler, so any SQL errors are raised to the calling routine, which in this case is SQL*Plus.

Example 11-32. Without an error handler in a block, PL/SQL errors are raised to SQL*Plus, and trigger the WHENEVER logic

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

However, you can rewrite the block in Example 11-32 so it includes an error handler. In that case, the PL/SQL error handler would get the error, and SQL*Plus wouldn't know about it. Example 11-33 shows the rewritten block.

Example 11-33. An error handler in a PL/SQL block "hides" errors from SQL*Plus

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 is 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 be available in all installations. You call it like this:

RAISE_APPLICATION_ERROR ( error_code , error_message );

in which:

error_code

Is a negative number. The range from -20000 to -20999 is reserved for user -defined errors.

error_message

Is a text message of up to 2048 characters .

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 to return the error to SQL*Plus. When that happens, SQL*Plus prints the error message and takes whatever action you specified in the most recent WHENEVER SQLERROR command. The PL/SQL block in Example 11-34 is the same as in Example 11-33, except for the addition of the RAISE_APPLICATION_ERROR procedure call, which is used to notify SQL*Plus of an error.

Example 11-34. However, you can use RAISE_APPLICATION_ERROR to pass an error up the line to SQL*Plus

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; /

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

11.5.1.4 WHENEVER OSERROR

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 similarly to the WHENEVER SQLERROR command. The simple version, which 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 to allow it to detect the error:

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 apply, however, and you can return an arbitrary value, the value from a bind variable, or the value of a substitution variable.