PL/SQL block problem: No data found error

SqlOraclePlsqlOracle10gOra 01403

Sql Problem Overview


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

  1. 00000 - "no data found" *Cause:
    *Action:

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

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

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;

Solution 3 - Sql

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

Solution 4 - Sql

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.

Solution 5 - Sql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionOrappsView Question on Stackoverflow
Solution 1 - Sqlbrian newmanView Answer on Stackoverflow
Solution 2 - SqlJohn HarrisView Answer on Stackoverflow
Solution 3 - SqlpjpView Answer on Stackoverflow
Solution 4 - SqlAdam PaynterView Answer on Stackoverflow
Solution 5 - SqlAnkur NirmalkarView Answer on Stackoverflow