Why is no_data_found ORA-01403 an exception in Oracle?

Viewed 86671

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?

7 Answers

MAX function works it does not throws error ORA-01403 works when NULL is returned by select INTO

Related