PLSQL 5

From bd_en
Jump to: navigation, search

Translated by Alexandru Martiniuc on 23.03.2018.


Exceptions

More details on exceptions can be found in the official Oracle documentation: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm


Why we use exceptions

Many programmers choose to think superficially and write code that meets the original specifications and that's it. They hope that users will only use the code according to those specifications and that no bug will occur.Oh, well... . Besides them, there are good programmers who are aware that whenever users or even their colleagues programmers who work on other application modules, can do something outside of those specifications and write their code accordingly. For example, the user can call a procedure with some invalid parameters and the programmer colleague may omit to write a validation against those values. In the end for us everything is reduced to a choice: you do enough and write the application in such a way as to protect the users in our case , the database of possible errors. I'd like to think we're in the second category.

Fortunately, PL / SQL gives us a powerful and flexible way to handle exceptions and give users the most useful messages in different scenarios. And as long as we use the exceptions correctly, it does not even require extra code to predict and deal with possible errors. In the following sections we will see what an exception in PL / SQL is, how it is defined, and especially how to catch and forward it to the caller.

When we use exceptions

We will start from an example, namely a function that receives as argument a student's registration number and returns the most recent grade of that student.

Here's a first version of the code for this procedure:

CREATE OR REPLACE FUNCTION student_recent_grade( pi_registration_number IN CHAR) RETURN VARCHAR2 AS recent_grade INTEGER; message VARCHAR2(32767); BEGIN SELECT value INTO recent_grade FROM (SELECT value FROM grades WHERE registration_number = pi_registration_number ORDER BY grading_date DESC ) WHERE rownum <= 1; mesaj := 'The latest student grade with registration_number ' || pi_registration_number || ' is ' || recent_grade || '.'; RETURN message; END student_recent_grade;

This is a code from the first category I was talking about earlier. It respects the initial specifications, namely to return the most recent student grade, you can present it to a lab, and possibly a lean teacher will give you the maximum score, no matter if he can tell you that you could add something. Now let's see what could be added to this code and for this we start from possible errors or possible irrelevant values returned by this function. We will run the following two selections and here are their results:

select student_recent_grade(-1) from dual;
select student_recent_grade(120) from dual;

To treat in a more useful way for the user these cases will make the following changes in the procedure:

 
   
  CREATE OR REPLACE FUNCTION student_recent_grade(
    pi_registration_number IN CHAR)
  RETURN VARCHAR2
AS
  recent_grade INTEGER;
  message        VARCHAR2(32767);
  counter      INTEGER;
BEGIN
  SELECT value
  INTO recent_grade
  FROM
    (SELECT values
    FROM grades
    WHERE registration_number = pi_registration_number
    ORDER BY grading_date DESC
    )
  WHERE rownum <= 1;
  message        := 'The latest student grade with registration_number ' || pi_registration_number || ' is' || recent_grade || '.';
  RETURN message;
EXCEPTION
WHEN no_data_found THEN
  SELECT COUNT(*) INTO counter FROM students WHERE registration_number = pi_registration_number;
  IF counter = 0 THEN
    message   := 'Student with registration number ' || pi_registration_number || ' doesn't exist in the database.';
  ELSE
    SELECT COUNT(*) INTO counter FROM grades WHERE registration_number = pi_registration_number;
    IF counter = 0 THEN
      message   := 'Student with registration_number ' || pi_registration_number || ' doesn't have any grade.';
    END IF;
  END IF;
RETURN message;
END student_recent_grade;

As you have seen, a new section appeared in the function / procedure / anonymous block, namely:

declare
begin
exception
end;


Let's run the two function calls again: Let's run the two function calls again:

select nota_recenta_student(-1) from dual;
select nota_recenta_student(120) from dual;

Which version does it look ok for the application user? What message do you prefer to show : "No data found" or "Student with registration number -1 doesn't exist"?

Types of exceptions

There are three types of exceptions (technically speaking two, plus a third so generic that it deserves a separate category):

  • exceptions defined by the user - declared as an exception object and initialized using the PRAGMA EXCEPTION_INIT function

with an error code between -20001 and -20999.

declare
  inexistent_student exception;
  PRAGMA EXCEPTION_INIT(inexistent_student, -20001);
begin
  if...then
     raise inexistent_student;
  end if;
  exception
    when inexistent_student then
      [do something]
end;
  • predefined exceptions in the database - remember that predefined exceptions have error codes between -1 (unique constraint violated)

and -20000. When defining our exceptions, it is recommended that we use error codes beginning with -20001 in order not to intersect with those codes already used or reserved by Oracle. The end of the range is -20999, so we can define a maximum of one thousand custom exceptions.

Some examples of predefined exceptions: DUP_VAL_ON_INDEX - A program attempts to store duplicate values in a column that is constrained by a unique index. NO_DATA_FOUND - A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. ZERO_DIVIDE - A program attempts to divide a number by zero.

begin
   [code]
   exception
     when NO_DATA_FOUND then
       [do something]
end;
  • the predefined exception OTHERS which the database throws in the case of any exception other than predefined and defined by the user.
begin
   [code]
   exception
     when NO_DATA_FOUND then
        [do something]
     when OTHERS then
        [do something]
end;

We can use WHEN OTHERS to show generic messages like "Unexpected error. Please contact the application administrator".

Another way to use WHEN OTHERS is the syntax EXCEPTION WHEN OTHERS THEN NULL that ignores any exceptions thrown by our code. I hope it is useless to specify never to use this construction in an application :) The only acceptable use of it is in a test code, for example if we want to display something and deliberately ignore any exception. I repeat, just for tests.

How we use an exception

There are 2 ways to use an exception:

  • throw the exception in a certain scenario (simpler version). In this case, we do not explicitly define the exception but just send the caller an error code and a message:

CREATE OR REPLACE FUNCTION student_recent_grade(
    pi_registration_number IN CHAR)
  RETURN VARCHAR2
AS
  recent_grade INTEGER;
  message      VARCHAR2(32767);
  counter      INTEGER;
BEGIN
  SELECT value
  INTO recent_grade
  FROM
    (SELECT value
    FROM grades
    WHERE registration_number = pi_registration_number
    ORDER BY grading_date DESC
    )
  WHERE rownum <= 1;
  message        := 'The latest student grade with registration_number ' || pi_registration_number || ' is' || recent_grade || '.';
  RETURN message;
EXCEPTION
WHEN no_data_found THEN
  SELECT COUNT(*) INTO counter FROM students WHERE registration_number = pi_registration_number;
  IF counter = 0 THE
    raise_application_error (-20001,'Student with registration number ' || pi_registration_number || ' doesn't exist in the database.');
  ELSE
    SELECT COUNT(*) INTO counter FROM grades WHERE registration_number = pi_registration_number;
    IF counter = 0 THEN
      raise_application_error (-20002,'Student with registration_number ' || pi_registration_number || ' doesn't have any grade.');
    END IF;
  END IF;
END student_recent_grade; 

If the student does not exist in the database, we return the caller ID -20001 and a personalized message, and if the student exists but has no notes, we return the error code -20002 and another message.

  • we define the exception, call it and then throw it (this is the complicated version but with the advantage that we can reuse the declared exceptions).
CREATE OR REPLACE FUNCTION student_recent_grade(
    pi_registration_number IN CHAR)
  RETURN VARCHAR2
AS
  recent_grade       INTEGER;
  message              VARCHAR2(32767);
  counter            INTEGER;
  inexistent_student EXCEPTION;
  PRAGMA EXCEPTION_INIT(inexistent_student, -20001);
  student_fara_note EXCEPTION;
  PRAGMA EXCEPTION_INIT(student_without_grades, -20002);
BEGIN
  SELECT COUNT(*) INTO counter FROM students WHERE registration_number = pi_registration_number;
  IF counter = 0 THEN
    raise inexistent_student;
  ELSE
    SELECT COUNT(*) INTO counter FROM grades WHERE registration_number = pi_registration_number;
    IF counter = 0 THEN
      raise student_without_grades;
    END IF;
  END IF;
SELECT value
INTO recent_grade
FROM
  (SELECT value
  FROM grades
  WHERE registration_number = pi_registration_number
  ORDER BY grading_date DESC
  )
 WHERE rownum <= 1;
  message        := 'The latest student grade with registration_number ' || pi_registration_number || ' is' || recent_grade || '.';
  RETURN message;
EXCEPTION
WHEN inexistent_student THEN 
	raise_application_error (-20001,'Student with registration number ' || pi_registration_number || ' doesn't exist in the database.');
WHEN student_without_grades THEN
  raise_application_error (-20002,'Student with registration_number ' || pi_registration_number || ' doesn't have any grade.');
END nota_recenta_student; 

The result is the same, except we have defined the exception. This style has the advantage that we could, in a separate set of exceptions , define a list of exceptions, each with its code and message. It is more elegant to define the exceptions in one place and just use them in different places. This avoids duplication of code and hardcoding.

exception
when exceptions.inexistent_student then
[code]
end;


Exercises

These exercises were inspired from a previous laboratory proposed by mr. Lucian Lazar

Exercice 1 (5 pt). Create a table that will contain all the students that went on a erasmus scholarship. The table must contain a registration number of the student, his first and last name and a number symbolizing the country he went.

  • (1pt). Create an unique constraint on the field representing the registration number.
  • (2pt). Woth respect to the uniquely constraint in the first part, in the newly created table copy 100 random students (from the table students). Some of the 100 students will be copied and some will violate the unique constraint.
  • (2pt). Display all the students two students that were successfully copied and two that were not copied.

Exercice 2 (5pt).

  • (1pt) Create a function or procedure that will increase the scholarship of each student with a hardcoded value.
  • (3pt) Call the procedure from an anonymous PLSQL block where you will also catch a custom exception thrown by the function when the increased scholarship has a value higher than 3000. In this case, you will also make the scholarship to have a value of 3000.
  • (1pt) Display a list of students that have scholarship, the new scholarship and how much was the scholarship increased.