Overview of PL/SQL Error Handling
When the system throws a warning or has an error it can lead to an exception. Such exception needs to be handled and can be defined internally or user defined. Exceptions stop normal execution. Internal exceptions are the ones handled by system like divide by zero, out of memory etc.
Advantages of PL/SQL Exceptions
They improve readability of the code by separating the error routines in the code. Exceptions help you handle errors. Each and every point of error need not be checked as exception handler can be added to the PL/SQL block.
Explain some of the commonly used Predefined PL/SQL Exceptions.
1. Divide by zero – This is raised when any number is attempted to divide by zero.
2. TOO MANY ROWS- A SELECT INTO statement returns more than one row.
3. CASE_NOT_FOUND- No choice in the WHEN clause of a case statement is selected.
4. LOGIN_DENIED- An attempt to login with an invalid username or password.
5. PROGRAM_ERROR- An internal PL/SQL problem.
What is user-defined exception? Explain it with an example.
A user-defined exception is what the programmer defines depending on the needs. These exceptions need to be explicitly raised.
Example:
DECLARE
salary_due EXCEPTION;
They can be raised by giving the command RAISE salary_due.
Explain how PL/SQL exceptions are raised.
PL/SQL exceptions are raised using the RAISE command. This command is used when exceptions are defined by programmer and not implicit exceptions.
Example:Declare and raising an exception
DECLARE
short_of_attendance EXCEPTION;
min_attendance NUMBER(4);
BEGIN
...
IF min_attendance < 10 THEN
RAISE short_of_attendance;
END IF;
EXCEPTION
WHEN short_of_attendance THEN
-- handle the error
END;
Illustrate reraising a PL/SQL Exception.
Reraising an exception is possible by handling it locally
Example:
DECLARE
out_of_attendance EXCEPTION;
BEGIN
...
BEGIN ---------- sub-block begins
...
IF ... THEN
RAISE out_of_attendance; -- raise the exception
END IF;
EXCEPTION
WHEN out_of_attendance THEN
-- handle the error
RAISE; -- reraise the current exception
END; ------------ sub-block ends
EXCEPTION
WHEN out_of_attendance THEN
-- handle the error differently
...
END;
Demostrate how to handle raised PL/SQL exceptions.
Raised exceptions are caught using exception handlers that help handling the exceptions to let the system know what suitable action needs to be taken.
Example: When the exception is raised, sequence of statements let the system know what action needs to be taken.
EXCEPTION
WHEN exception_name1 THEN -- handler
sequence_of_statements1
WHEN exception_name2 THEN -- another handler
sequence_of_statements2.
Tips for handling PL/SQL errors.
1. When an exception is raised it should be handled well so that system does not collapse.
2. When an exception is raised, retry the transaction rather than abandoning it.
3. Make use of locator variables to track statement executions.
