Menu
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.
789
Answer: To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned. For example: -- Check to make sure that at least one record is returned SELECT COUNT(1) INTO v_count FROM sales.
If you attempt to access the updated files at a later date, the ORA-01403 error will occur. To fix this, re-create tables from the initial controlling database.
Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query. Action: Terminate processing for the SELECT statement.
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 ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
