EXCEPTIONS

In order to understand and work with exceptions go ahead and create a table called COURSE with the following structure and insert these rows into that table.

We will take an example to show why and where we use exception so that we will understand it better.

DECLARE
    var_courseid course.course_id%type; -- declaring the variable for course_id
    var_holdcoursename course.course_name%type; -- variable to hold the course_name
BEGIN
    var_courseid := 1001; -- Assinging the value to the variable
    SELECT course_nm into var_holdcoursename FROM course where course_id = var_courseid;
    dbms_output.put_line(var_holdcoursename); --output the selected value
END;
If you execute the above PL/SQL block then we will get JAVA as the output. The course id 1001 exists in the table so we got the result as we expected. Now we will take the next example

DECLARE
   var_courseid course.course_id%type;
   var_holdcoursename course.course_name%type;
BEGIN
   var_courseid := 1009;
   SELECT course_nm into var_holdcoursename FROM course where course_id = var_courseid;
   dbms_output.put_line(var_holdcoursename);
END;

Try to execute the above block. Now there is no row for course id 1009. So we will end up in an error which says no_data_found ie predefined oracle error message from the server. We expect to get a message saying that there is no such customer exists in the database instead of an error. So what we should do, that is the place exception comes into picture in PL/SQL block.

So an Exception is nothing but handling errors. ( Expected and Un Expected errors).

So now we will write the same block which includes exception handling.

DECLARE
    var_courseid course.course_id%type;
    var_holdcoursename course.course_name%type;
BEGIN
    var_courseid := 1009;
    SELECT course_nm into var_holdcoursename FROM course where course_id = var_courseid;
    dbms_output.put_line(var_holdcoursename); --output the selected value
EXCEPTION
   WHEN no_data_found then
    dbms_output.put_line('Course ID ' || to_char(var_courseid) || ' does not exists');
END;

So for the above example you will get Course ID 1009 does not exists instead of error. This is what handling of an exception is. So its easy. We will see some more examples and how to handle different types of exceptions.

To remember while writing Exceptions

When an exception raised in a PL/SQL block it branches to the corresponding exception section of the block. Once the control goes to the exception section we cannot branch it to the the main section.

When you are handling WHEN OTHERS exception clause after all other exception handling clauses.

There are two types of Exception. Predefined Exceptions and User Defined exceptions.

Predefined Exception

In the above example shown there is WHEN no_data_found, what exactly it means is when ever a select issued to the database and if that select does not return any row back oracle raises an exception called no_data_found so that we can hadle it in the exception section of the PL/SQL block. The following are the predefined exceptions available in the oracle PL/SQL.

User Defined Exception
Its an error defined by the program so that we can implement some of the business rules in the application. Where you declare the exception in a PL/SQL block. We will take an example now where we declare the user defined exception.

DECLARE
   exp_toomuchsal EXCEPTION;
   var_raisesal number(6,2);
   var_holdsalary number(8,2);
   var_empno employee.empno%type;
BEGIN
   Select salary into var_holdsalary from employee where empno = var_empno;
   If var_raisesal > (var_holdsalary * .10) then
         raise exp_toomuchsal;
   End If;
   Update employee set salary = salary + var_raisesal where empno = var_empno;
EXCEPTION
   WHEN exp_toomuchsal then
    raise_application_error(-20000,'Salary is more than 10% of the current salary, cannot do an update');
END;

Raise_application_error function

is used to issue the user defined error message from the pl/sql programs.
The syntax is
raise_application_error( error_number, error_text)
error_number argument for the function should be between -20000 and -20999 and error_text can be up to 2048 characters length.
Once the block raises the error which caused by raise_application_error then the error number and message can be trapped like any other oracle error.

Block with in a Block

Following is an example where you implement blocks with in blocks.

DECLARE
  var_courseid course.course_id%type;
  var_holdcourseid course.course_id%type;
  var_holdcoursename course.course_name%type;
BEGIN
  var_courseid := 1004;
  BEGIN
         Select course_name into var_holdcoursename From course where course_id = var_courseid;
  EXCEPTION
         When no_data_found then
          insert into course values (var_courseid,'DEFAULT',sysdate,'JIM','DALLAS');
   END;
--Continue the process if you have any
END;

DECLARE
   var_number number(4);
BEGIN
   var_number := 99
   insert into temp_table values ( var_number , 'TESTING');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX then
    LOOP
         var_number := var_number + 1;
    BEGIN
          insert into temp_table values (var_number, 'TESTING');
    EXCEPTION
         When DUP_VAL_ON_INDEX then
         null;
    END;
    END LOOP;
END;