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.
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
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.
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
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;
Source: Stackoverflow.com