Oracle: No Data Found

ORA-01403: no data found

This is an error that happens in Oracle when you try and stuff nothing into a variable via a select statement.

SELECT id INTO tempvar FROM person WHERE name = 'Steve';

Most places will tell you to work with this problem through exceptions:

    SELECT id INTO tempvar FROM person WHERE first_name = 'Steve';
    -- what to do when no data is found

This seems strange to me, as this is not an exceptional case. You know there is a possibility that the data you want won’t be there. So why don’t you just check for it in the beginning.

I’d like to propose a more explicit solution to the problem.

SELECT count(*) into tempvar from person where first_name = 'Steve';
IF temp_variable <> 0
    --we're all good
    SELECT id INTO tempvar FROM person WHERE first_name = 'Steve';

Exceptions break the flow of a program. Do you really want to break out of what you’re doing or do you want to handle the missing row?

Several days after posting I came across a stackoverflow question regrading how to drop a table that may or may not exist. The first (and accepted) answer recommends catching the “table not found” exception, which is what I recommend against. The second answer, however,
makes a similar assertion about using a conditional.

Comment on that answer:

+1 This is better because do not relay on exception decoding to understand what to do. Code will be easier to mantain and understand – daitangio