PL/SQL block problem: No data found error

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, 4th Edition 2009" my problem is when I enter a student_id not present in the table it returns me the following error

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

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

Answer 1

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       ...