As I said in my question, "it would be even more awkward to have EXCEPTION handling after each SELECT INTO". This is exactly what I dislike.

Although this isn't a recommended method, the built-in MIN function has a curious side effect of trapping an ORA-1403 exception and returning a NULL instead. So this appears to do what Gary is asking for - (although not necessarily recommended for a production system).

SELECT MIN(XYZ) INTO V2 FROM T1 WHERE C1 = 'A';
IF V2 IS NOT NULL THEN
   <do something with V2>;
END IF; 

MIN function works with number, varchar2, date.

The down side might be that this method would hide a real error, such as exception ORA-01422 (exact fetch returns more than requested number of rows). So, you would have to be certain that there would be no case where the query could return more than one row.

Words of wisdom -
"Note that not only do existence checks add overhead, they aren't guaranteed to be safe (i.e. you can still have a no_data_found after this passes). This is due to Oracle's statement level read-consistency. If someone else deletes the row after the count(*) starts, but before the select into does the row is gone and you'll still get no_data_found. "

You write Bad code and you blame Oracle. Very nice !!!

Why are exceptions awkward ? What is the "Technical" reason.
You are only giving human psychological emotional reasons.

You were anyway doing the check some way or the other for the next action depending on the value.

Please give some technical reasons.

Why is there a 'D' in Fridge, but not in Refrigerator ?

Select x into variablefrom tab where ...

Will either
return one row value ..( This value itself can be a null in the column )
Raise Exception no data found
Raise Exception exact fetch returns too many rows


So my Dear, How will you distinguish whether it was no data found or actual null value if Oracle was just going to return just a null for Select x into var from tab .

Aggregations - is different
Select Max(x) into variable tab where ...
Will always return one row which has
A Value or
A Null value even if where clause is not satisfied.

"Don't you think this is something Oracle need to improve?"

No - You have to improve your coding skills here not Oracle . Okay !!!

"SELECT MIN(XYZ) INTO V2 FROM T1 WHERE C1 = 'A';
IF V2 IS NOT NULL THEN
<do something with V2>;
END IF;
"
This seems the best workaround. Thanks.

Other comments are not relevant. I know column XYZ is defined as NOT NULL, so when MIN returns NULL, I know the row does not exist. This is for batch process, so I don't have concerns about isolation level either.

If think raising exception in this case is the logic way or preferred, when you run the same SQL dynamically in SQL*Plus, do you also want Oracle to raise exception?

Further more, why does Oracle return NULL when SELECT MIN(XYZ), but raise exception when SELECT XYZ? It's just inconsistent.

So many cars get recalled. You cannot blame the drivers.

The problem of the original question seems to be

- Check for in one (or more?) rows
- process a column

In PL/SQL you can always try the FOR ... LOOP construct, and I am aware that some people may frown upon that solution, but it gets rid of any exception and works as expected.

DECLARE
BEGIN
  FOR rec IN (SELECT * FROM dual WHERE 0=1) -- no record
  loop
    dbms_output.put_line('do something A: ' || rec.dummy);
    EXIT;    
  END LOOP;

  FOR rec IN (SELECT * FROM dual) -- one or more records
  loop
    dbms_output.put_line('do something B: ' || rec.dummy);
    EXIT;    
  END loop;
END;
/

Our coding standards state: if you do a SELECT INTO, you must explicitly handle NO_DATA_FOUND and TOO_MANY_ROWS, or provide a comment that explains why they are being allowed to be raised.

There is no reason to 'not think about' the possibility of these exceptions.
Having thought about it, there is no reason to not share your thoughts with future generations vie either an exception handler or a comment.

Read and understand this article carefully and rethink what it is you're trying to accomplish:

http://tkyte.blogspot.nl/2008/12/doing-it-wrong.html

Chances are that you can accomplish your goal with fewer PL/SQL, perhaps even a 100% SQL solution.
After reading and understanding Tom's article, I discovered I could rewrite (and speed up) lots of legacy code.
The 'count_rows_before_processing_them'-approach is completely flawed in the first place.


"Don't you think this is something Oracle need to improve? Do you have a better solution w/o using EXCEPTION handling?"
Yes, minimize PL/SQL and maximize SQL.
Think in SETS instead of ROWS.

What is a better replacement of the code below?

SELECT COUNT(*) INTO L_DAY FROM HOLIDAY_TABLE
WHERE HOLIDAY = 'Y' AND DATE_ BETWEEN SYSDATE AND SYSDATE + 7;
-- this is first place where a developer used COUNT(). I would change it to MIN(DATE_)

IF L_DAY > 1 THEN
SP_SEND_EMAIL_EMPLOYEES;
END IF;

If say just run SP_SEND_EMAIL_EMPLOYEES, then still have to read the HOLIDAY_TABLE inside the SP.

select * from employees
where ...
and   exists (
  select null 
  from   holiday_table 
  where  holiday = 'Y' 
  and    date_ between sysdate and sysdate + 7);

"If say just run SP_SEND_EMAIL_EMPLOYEES, then still have to read the HOLIDAY_TABLE inside the SP."

Yes, and that usually is the safest and fastest way.
Since we don't know about your data or database version, nor do we know about what it is that SEND_EMAIL does ( but it will probably send some emails based on some conditions ), you should simply be able to join. Databases are born to join and crunch entire sets at once. Doing the same row-by-row (slow-by-slow) done on a count(*)-bases condition is only causing context switching and degrading performance, sometimes severely, based on statistics/database version. If you think you need a count(*), then you've forgotten that you already know what you need. Don't blame Oracle, give it sets and not a row per PL/SQL iteration. You only do that when SQL cannot do it.

"instead of returning NULL"
You're either a front-end developer or facilitating one? ;)

Anyway: feel free to provide a more detailed (but concise) case. How is the email being sent, for example?

1. If you use approach with min/max you should use count to check if there was only one record:

SELECT MIN(field), COUNT(1)
  INTO a, b
  FROM tab
 WHERE ...

CASE b
  WHEN 1 THEN ... -- ok
  WHEN 0 THEN ... -- no_data_found
  ELSE ... -- too_many_rows
END CASE

2. You can do like this:

SELECT (SELECT field FROM tab WHERE ...)
  INTO a
  FROM Dual

This will hide no_data_found, but leave to_many_rows (you get ORA-01427 insted of ORA-01422) what I think is good.

3. I like best for loop:

for i in (SELECT field, COUNT(1) OVER() cnt FROM tab WHERE ...)
LOOP
  IF i.cnt = 1 THEN ... -- ok
    ELSE ... -- too_many_rows
  END IF;
END LOOP;

But absolutely best method is to handle exception what you are trying to avoid. This is safest, fastest, uses least resources and is most readable and uderstandable. If someone will look at your "SELECT min(x) INTO a" he will not puzzle out your intents, he will uderstand code exactly the way it is written. Do not teach your programmers bad things. It is lame what you want to do. Oracle do not need to change anythig about this one. NEVER! This is the way it should be.

And never say "exeption block takes extra lines of code".
It is absolutely dumbest argument I've ever heard.
You should judge programm by the way it woks, not by the count of lines.