CRITICAL SKILL 6.5 Handle Error Conditions in PL/SQL

Team Fly

Page 217

CRITICAL SKILL 6.5
Handle Error Conditions in PL/SQL

As we have seen in the previous section, bad things happen to good programs. However, you also have to deal with bad or problematic data as well. To deal with problems during the processing of data, PL/SQL provides us with the robust ability to handle these types of errors. We call this type of program code exception handling.

To raise an error from within a PL/SQL program, use the built-in function named raise_application_error. The function requires two arguments. One is for the error number. This number must be between −20000 and −20999. The second argument is the error that you want the user to see.

As with all exception handling, this program code is placed into the EXCEPTION section of your PL/SQL program. Thus, our program structure will now be

BEGIN
  -- Put Program Here
EXCEPTION
-- Put exception handlers here
END;
/

Table 6-3 looks at some of the most common errors that Oracle helps you handle.

NOTE
You must always make the ''Others" error handle the last one in your program, since Oracle will not process any exception handles after this one.

Exception Name	Explanation	Oracle Error
No_data_found	When a select statement returns no rows, this error may be raised. It usually occurs when you use an implicit cursor and perform a SELECT INTO.	ORA-01403
Too_many_rows	When a case that should only return a single row returns multiple rows, this exception is raised.	ORA-01422
Dup_val_on_index	This exception is raised when you try to insert a record into a table that has a primary key on it and the record that you are inserting is a duplicate of one that already exists in the table.	ORA-00001

Team Fly