PLSQL Exception Handling

Question

I need to make my program print an error when the student number is invalid using an exception section in two places. One in the main procedure and one in the sub-program.

How would I make an error so that the exception in the sub-program runs but not in the main program and vice versa?

CREATE OR REPLACE PROCEDURE validate_student(
  p_snum IN students.snum%TYPE,
  p_student_error out VARCHAR2) AS

  v_snum students.snum%TYPE;

BEGIN
  --count the number of student with id p_snum and assign it to v_snum
  SELECT count(snum) INTO v_snum
  FROM students
  WHERE snum = p_snum;

  --if count is 0 then the student does not exist in the database, return error.
  IF v_snum = 0 THEN
    p_student_error := 'Student number ' || p_snum || ' is invalid. ';
  END IF;
END;
/

--This procedure checks if course id is valid
CREATE OR REPLACE PROCEDURE validate_course(
  p_callnum IN enrollments.callnum%TYPE,
  p_course_error out VARCHAR2) AS
  v_callnum enrollments.callnum%TYPE;
BEGIN
  --count the number of course with id p_callnum and assign it to v_callnum
  SELECT count(callnum) INTO v_callnum
  FROM schclasses
  WHERE callnum = p_callnum;

  --if count(callnum) = 0 then course does not exists in database, return error.
  IF v_callnum = 0 THEN
    p_course_error := 'Call number '|| p_callnum ||' is invalid .';
  END IF;
END;
/

--This function check the current amount of students enrolled in the course
CREATE OR REPLACE FUNCTION course_capacity(
  p_callnum enrollments.callnum%TYPE)
  RETURN VARCHAR2 IS

  v_current_enrolled NUMBER(3);
  v_capacity NUMBER(3);
  v_capacity_error VARCHAR2(200);
BEGIN
  --count number of students enrolled and assign it to v_current_enrolled
  SELECT count(snum) INTO v_current_enrolled
  FROM enrollments
  WHERE callnum = p_callnum
  AND grade is null;

  --check maximum capacity of the course and assign it to v_capacity
  SELECT capacity INTO v_capacity
  FROM schclasses
  WHERE callnum = p_callnum;

  --if current amount of students enrolled < maximum capacity then class is not full
  --else class is full.
  IF v_current_enrolled < v_capacity THEN
    v_capacity_error := NULL;
  ELSE
    v_capacity_error := 'Course number ' || p_callnum || ' is full. ';
  END IF;

  RETURN v_capacity_error;
END;
/

--This function check the units a student currently enrolls.
CREATE OR REPLACE FUNCTION student_unit_limit(
  p_snum students.snum%TYPE,
  p_callnum schclasses.callnum%TYPE)

  RETURN VARCHAR2 IS

  v_student_crhr NUMBER(2);
  v_course_crhr courses.crhr%TYPE;
  v_crhr_error VARCHAR2(200);

BEGIN
  --count the current units the student has, and assign it to v_student_crhr
  SELECT nvl(sum(crhr),0) INTO v_student_crhr
  FROM courses c INNER JOIN schclasses sc ON c.dept = sc.dept
                                                              AND c.cnum = sc.cnum
                         INNER JOIN enrollments e ON sc.callnum = e.callnum
  WHERE e.snum = p_snum
  AND grade IS NULL;

  --find the credit hour of the course
  SELECT crhr INTO v_course_crhr
  FROM courses c INNER JOIN schclasses sc ON c.dept = sc.dept
                                                            AND c.cnum = sc.cnum
  WHERE sc.callnum = p_callnum;

  --if current credit hour of student + the credit of the class <= 15 then
  --student hasn't reached maximum allowed units and can enroll in the course.
  IF v_student_crhr + v_course_crhr <= 15 THEN
    v_crhr_error := NULL;
  ELSE
    v_crhr_error := 'Max units allowed is exceeded. ';
  END IF;
  RETURN v_crhr_error;
END;
/

--This procedure combine all 4 of procedures and functions above,
CREATE OR REPLACE PROCEDURE addme(
  p_snum students.snum%TYPE,
  p_callnum schclasses.callnum%TYPE) AS

  v_error_text VARCHAR2(200);
  v_error_msg VARCHAR2(200);

BEGIN
    validate_student(
    p_snum,
    v_error_text);

    v_error_msg := v_error_text;

    validate_course(
    p_callnum,
    v_error_text);

    v_error_msg := v_error_msg || v_error_text;

    IF v_error_msg IS NOT NULL THEN
      dbms_output.put_line(v_error_msg);
    ELSE
      v_error_msg := course_capacity(p_callnum) || student_unit_limit(p_snum, p_callnum);
      IF v_error_msg IS NOT NULL THEN
        dbms_output.put_line(v_error_msg);
      ELSE
        INSERT INTO enrollments VALUES (p_snum, p_callnum, NULL);
        COMMIT;
        dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);
      END IF;
    END IF;
END;
/

The AddMe procedure is the main program while the others are subprograms.

Answer 1

I would propose to make a package:

create or replace package students_pkg as 

-- declare an exception
student_enroll_error exception;
-- bind error code -20001 to your custom exception
pragma exception_init (student_enroll_error, -20001);

-- the only visible procedure
procedure addme(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type);

end students_pkg;
/

create or replace package body students_pkg as 

procedure validate_student(
  p_snum in students.snum%type) as

  v_snum students.snum%type;
begin
  -- try to find a student
  select snum into v_snum
  from students
  where snum = p_snum;

exception
  -- if student not found, an no_data_found exception is rised
  when no_data_found then
    -- re-raise exception with custom code:
    raise_application_error(-20001, 'Student number ' || p_snum || ' is invalid.');
end;

-- the same logic as in the previous procedure
procedure validate_course(p_callnum in enrollments.callnum%type) is
  v_callnum enrollments.callnum%type;
begin
  select callnum into v_callnum
  from schclasses
  where callnum = p_callnum;

exception
  when no_data_found then
    raise_application_error(-20001, 'Call number '|| p_callnum || ' is invalid .');
end;

--This function check the current amount of students enrolled in the course
procedure check_course_capacity(p_callnum enrollments.callnum%type) is
  v_current_enrolled number(3);
begin
  -- here I combined queries. The idea is to make one query, which 
  -- returns one row if a course is not full and 0 rows, if it is full
  select count(e.snum) into v_current_enrolled
  from enrollments e
       join schclasses s on e.callnum = s.callnum
  where grade is null
  group by s. capacity
  having s. capacity > count(e.snum);

exception
  -- here no_data_found means that course is full
  when no_data_found then
    raise_application_error(-20001,  'Course number ' || p_callnum || ' is full. ');
end;

-- the same idea as in previous procedure, but I am not sure I fully understood the logic.
-- You can elaborate it
procedure check_student_unit_limit(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type) is

  v_course_num courses.cnum%type;
begin

  select c.cnum into v_student_crhr
  from courses c inner join schclasses sc on c.dept = sc.dept and c.cnum = sc.cnum
                 inner join enrollments e on sc.callnum = e.callnum
  where e.snum = p_snum
    and grade is null
  group by c.cnum
 having sum(crhr) <= 15;

exception
  when no_data_found then
    raise_application_error(-20001,  'Max units allowed is exceeded. ');
end;

procedure addme(
  p_snum students.snum%type,
  p_callnum schclasses.callnum%type) is

begin
  validate_student(p_snum);
  validate_course(p_callnum);
  check_course_capacity(p_callnum);
  check_student_unit_limit(p_snum, p_callnum);

  insert into enrollments values (p_snum, p_callnum, null);
  -- I would not recommend you to use commit here. Call it outside this procedure
  commit;
  dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);

exception
  -- if custom exception had been raised, you will get the second parameter,
  -- passed to raise_application_error, in SQLERRM function:
  when student_enroll_error then
    dbms_output.put_line(sqlerrm);
end;

end students_pkg;
/

Answer 2

Addition to @Dmitry answer. it is always better to enclose each procedure with a Begin and End block and exception handle for each call.So you know exactly where the exception occurred.

    procedure addme(
    p_snum students.snum%type,
    p_callnum schclasses.callnum%type) is

    -- bind error code -20001 to your custom exception
    pragma exception_init (student_enroll_error, -20001);
    begin

    Begin
      validate_student(p_snum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
      validate_course(p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
    check_course_capacity(p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;

      Begin
    check_student_unit_limit(p_snum, p_callnum);
    exception
      --handle all the exceptions which  you know of specifically
      when no_data_found then
          --log the error
           dbms_output.put_line(sqlerrm);
      --default handler
      when other then 
          dbms_output.put_line(sqlerrm);
    end;


    insert into enrollments values (p_snum, p_callnum, null);
    -- I would not recommend you to use commit here. Call it outside this procedure
    commit;
    dbms_output.put_line('You have successfully enrolled in course number ' || p_callnum);

    exception
    -- if custom exception had been raised, you will get the second parameter,
    -- passed to raise_application_error, in SQLERRM function:
    when student_enroll_error then
      dbms_output.put_line(sqlerrm);
    end;