Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

11.5 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.

The default behavior of WHENEVER SQLERROR EXIT is to COMMIT any pending transaction. You may want to use the ROLLBACK option to change that behavior. Using WHENEVER SQLERROR EXIT with the GUI version of SQL*Plus can be annoying at times. Any error results in SQL*Plus terminating, causing the GUI window to close. Usually this happens before you realize an error occurred, making you miss any displayed error message. Do not use the keyword THEN in your statement. It's WHENEVER SQLERROR EXIT, not WHENEVER SQLERROR THEN EXIT. I often tend to get mixed up on this point.

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.

You can use the BITAND function to predict the actual, modulo 256 value that will be returned for a given error code: SQL> SELECT BITAND(1555, 255) 2 FROM dual; BITAND(1555,255) ---------------- 19 This output tells you that an ORA-01555 error would be passed back to Unix as a 19, which is helpful to know if you want your shell script to take action on that specific error.

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.