PL/SQL Exception

EXCEPTION Handling is made to handle errors that arise during the execution of the code. These errors can be categorized into two types:
1. Predefined exceptions
   Oracle Corporation has pre-defined several exceptions:

Exception Name Reason Error Number

  CURSOR_ALREADY_OPEN

When you open a cursor that is already open.

ORA-06511

  INVALID_CURSOR

When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.

ORA-01001

  NO_DATA_FOUND

When a SELECT…INTO clause does not return any row from a table.

ORA-01403

  TOO_MANY_ROWS

When you SELECT or fetch more than one row into a record or variable.

ORA-01422

  ZERO_DIVIDE

When you attempt to divide a number by zero.

ORA-01476

For Example, the code below showing how to handle the predefined exception:

BEGIN
     <Execution section>
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line ('SELECT statement did not return any row.');
END;

2. User-defined exceptions

In addition to the predefined exceptions, we can explicity define our own exceptions, or raise an exception with the RAISE command. For Example:. we want to raise an exception if the input number given to function find_total is negative

DECLARE
     inputNo1 NUMBER := -2;

     --define an exception (user-defined) with name err_must_positive
     err_must_positive EXCEPTION;

--create function find_total
FUNCTION find_total (inputNo NUMBER) RETURN NUMBER
IS
BEGIN
     IF (inputNo >= 0)
     THEN
       RETURN(inputNo+20);
     ELSE
       RAISE err_must_positive;
     END IF;
END find_total;

BEGIN
   no1 := find_total(inputNo1);
   EXCEPTION
     WHEN err_must_positive THEN
     dbms_output.put_line('Number must be positive');
END;

 

References :

 

 

Leave a comment