PL/SQL block problem: No data found

Question

SET SERVEROUTPUT ON
DECLARE
 v_student_id NUMBER := &sv_student_id;
 v_section_id NUMBER := 89;
 v_final_grade NUMBER;
 v_letter_grade CHAR(1);
BEGIN
 SELECT final_grade
 INTO v_final_grade
 FROM enrollment
 WHERE student_id = v_student_id
 AND section_id = v_section_id;

 CASE -- outer CASE
  WHEN v_final_grade IS NULL THEN
   DBMS_OUTPUT.PUT_LINE ('There is no final grade.');
  ELSE
   CASE -- inner CASE
    WHEN v_final_grade >= 90 THEN v_letter_grade := 'A';
    WHEN v_final_grade >= 80 THEN v_letter_grade := 'B';
    WHEN v_final_grade >= 70 THEN v_letter_grade := 'C';
    WHEN v_final_grade >= 60 THEN v_letter_grade := 'D';
    ELSE v_letter_grade := 'F';
   END CASE;

   -- control resumes here after inner CASE terminates
   DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade);
 END CASE;
 -- control resumes here after outer CASE terminates
END;

the above code i have taken from the book oracle pl-sql by example fourth edition 2009 my problem is when i enter a student id not present in the table it returns me the following errors

Error report: ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 -  "no data found"
*Cause:    
*Action:

but according to book it should have returned a null value and then follow the case flow..

please help me out...

thanks in advance

Answer 1

Might be worth checking online for the errata section for your book.

There's an example of handling this exception here http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm

Answer 2

Your SELECT statement isn't finding the data you're looking for. That is, there is no record in the ENROLLMENT table with the given STUDENT_ID and SECTION_ID. You may want to try putting some DBMS_OUTPUT.PUT_LINE statements before you run the query, printing the values of v_student_id and v_section_id. They may not be containing what you expect them to contain.

Answer 3

I do not know why the book thinks the above code should run because when you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to wrap the SELECT statement with it's own BEGIN/EXCEPTION/END block. Example:

...
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN

    BEGIN
    SELECT final_grade
      INTO v_final_grade
      FROM enrollment
     WHERE student_id = v_student_id
       AND section_id = v_section_id;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_final_grade := NULL;
    END;

    CASE -- outer CASE
      WHEN v_final_grade IS NULL THEN
      ...