PL/SQL error handling

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.
PL/SQL subprograms
PL/SQL subprograms - What are PL/SQL Subprograms?, What are the parts of PL/SQL Subprograms?, Advantages of PL/SQL Subprograms, PL/SQL has two types of subprograms called procedures and functions, How does subprograms pass information? What are actual and formal subprogram parameters...
PL/SQL packages
PL/SQL packages - What is a PL/SQL package?, PL/SQL Package, Advantages of PL/SQL Packages, What are the types and layers of Packages?, PL/SQL Packages features, Private vs. Public items in Packages...
PL/SQL triggers
PL/SQL triggers - What is a Trigger? Syntax of Triggers, Types of PL/SQL Triggers, PL/SQL trigger execution hierarchy, How to obtain information about any trigger?...
Post your comment

You may like