PL/SQL block problem: No data found error
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.
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 ...
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
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.
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;
This data not found causes because of some datatype we are using .
like select empid into v_test
above empid and v_test has to be number type , then only the data will be stored .
So keep track of the data type , when getting this error , may be this will help