PL/SQL block problem: No data found error
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 ...
There is an alternative approach I used when I couldn't rely on the EXCEPTION block at the bottom of my procedure. I had variables declared at the beginning:
my_value VARCHAR := 'default';number_rows NUMBER := 0;...SELECT count(*) FROM TABLE INTO number_rows (etc.)IF number_rows > 0 -- Then obtain my_value with a query or constant, etc.END IF;