Why is no_data_found ORA-01403 an exception in Oracle?

Question

If the SELECT INTO statement doesn't return at least one row, ORA-01403 is thrown.

For every other DBMS I know this is normal on a SELECT. Only Oracle treats a SELECT INTO like this.

CREATE OR REPLACE PROCEDURE no_data_proc IS
   dummy dual.dummy%TYPE;
BEGIN
  BEGIN 
     SELECT dummy  
       INTO dummy
       FROM dual
      WHERE dummy = 'Y';   
  EXCEPTION 
     WHEN no_data_found THEN
        dbms_output.put_line('Why is this needed?');
  END;
END no_data_proc;

Why?

In my opinion you don't need this exception really. It is too much overhead. Sometimes it is handy but you have to write a whole BEGIN, EXCEPTION, WHEN, END Block.

Are there any essential reasons I don't see?

Answer 1

Because you are doing SELECT INTO which requires exactly one row (more rows would also be an error).

If you can have one or no row, you can use a cursor.

It is not the database's job to decide for you that a missing row is not an error, and just set the value to null.

Answer 2

You can try use MIN for avoid use EXCEPTION clause.

 SELECT MIN(dummy)  
   INTO dummy
   FROM dual
  WHERE dummy = 'Y'; 

then dummy variable will be NULL

Answer 3

The exception block is not needed, you might use it or not, depending on the context.

Here you are actively ignoring the exception (the procedure will return successfully) but most of the time if you're doing a SELECT INTO you want it to fail if it doesn't return a row, consider:

PROCEDURE update_employee_salary (p_empno) IS
   l_salary NUMBER;
BEGIN
   SELECT sal INTO l_salary FROM emp WHERE empno = p_empno FOR UPDATE;
   /* do something with emp data */
END;

Here I want my function to fail if it is called with an empno that doesn't exist in the EMP table. I might catch the exception to raise a meaningful error message (with raise_application_error) but most of the time I'm happy with the ORA-01403.

In general, the only exceptions you should catch are the expected exceptions (i.e. this should not be the standard to catch all ORA-01403, or all exceptions for that matter).

Answer 4

But we still need to answer the question of "why is an exception thrown in the case where a SELECT has no data to be retrieved".

I believe this is done because it's a common situation which might otherwise be overlooked. Writing code as though it always expects to find data is a common thing to do, and if we were supposed to put in error checks such as

SELECT <something...>
IF SQLCODE = 100 THEN -- No data found
  <no-data handler>
END IF

it is likely IMHO that the check for SQLCODE = 100 would be skipped frequently. Having an exception raised rams it right up your nose that A) an important condition (no data found) occurred, and B) NO ALLOWANCE WAS MADE FOR THIS. IMO having the PL/SQL engine raise an exception is better than having the program continue merrily on its way under the assumption that data was retrieved when in fact it wasn't, which can lead to all sorts of other-than-merry problems.

Share and enjoy.

Answer 5

Because it's not clear what the PL/SQL engine should do - should it exit the block? Should it press on with NULL in the variable? What if in the next block you try to insert that into a NOT NULL column, how should it report the location of the error? Making it an exception forces you to be explicit about it.