PL SQL Tutorial

PL SQL Exceptions

In PL SQL There are 2 types of errors. 1. Compile Time Error 2. Run-time Errors. Exceptions comes under Run-Time Errors. Exception are raised by PL-SQL Code while executing. Exceptions can occur during program execution, may be due to hardware failure, storage error, some arithmetic error division by zero etc., Whenever there is a Exception in Pl-SQL Code, program will terminate abruptly.Programmer cannot anticiate all possible exceptions. Diligently Programmer can handle exceptions using Exception Handlers.

Exception Block is part of Excection Block. Exception Block can be included in Anonymous blocks, stored procedures, functions, and in Packages too.

There are 2 types of Exceptions

  • Database defined Exceptions, Built-in Exceptions
  • User-Defined Exceptions Custom Exceptions
NO_DATA_FOUNDWhen SELECT INTO query returns no rows found
TOO_MANY_ROWSRecord variable can hold single record at a time, When SELECT INTO Query returns more than one row,
OTHERSgeneral exception,i.e it can handle any type of exception. It should be last in the Exception Block
ZERO_DIVIDE Dividing any number by zero, raises this exception
VALUE_ERROR && INVALID_NUMBER INVALID_NUMBER occurs unable to convert charater String to number.
VALUE_ERROR ocuurs when truncation or constraint violation

Raising Exceptions

   Database can raise exceptions , this process is Automatic.
Programer can also raise exceptions manually or whenever he needs in the prorgam.

Propogating Exceptions