Select Max with Exceptions

[dr_gori]

Inaccurating the new "day-to-day errors" forum, I leave a classic mistake that once in a while I meet out there. Try to find the error:

Select all

Begin 
  Select max(cd_segurado_principal) 
  Into :bl_segurado.cd_segurado_principal 
  From tb_segurado 
  Where cd_cliente_segurado = :bl_segurado.cd_cliente_segurado; 
Exception 
  when too_many_rows THEN 
    pr_informacao('Há mais de um registro com o nome deste Segurado: ' 
                  ||CHR(13)||:bl_cliente.nm_cliente); 
  When no_data_found THEN 
    null; 
  When Others Then 
    pr_erro('Falha ao recuperar a próxima sequência para o Segurado: '||SQLERRM); 
End;

View: The guy who did this set up all exceptions right. The only thing is that none of them serve anything! A select max this way will always return a line, that is, it will not fall on too_many_rows or no_data_found nunquinha da silva!

Select all

SQL> select * from emp; 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL 
---------- ---------- --------- ---------- --------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250 
      7566 JONES      MANAGER         7839 02-APR-81       2975 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000 
      7839 KING       PRESIDENT            17-NOV-81       5000 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
      7876 ADAMS      CLERK           7788 23-MAY-87       1100 
      7900 JAMES      CLERK           7698 03-DEC-81        950 
      7902 FORD       ANALYST         7566 03-DEC-81       3000 
      7934 MILLER     CLERK           7782 23-JAN-82       1300 
 
14 rows selected. 
 
SQL> select max(sal)  
  2  from emp 
  3  where ename='THOMAS' 
  4  / 
 
  MAX(SAL) 
---------- 
 
 
SQL>  

Note that even not returning any row, the Select Max returns as null the value.

In addition, I have thousands of bizarre situations that I find in the programs. If you have things like that, wide here to learn from the mistakes of others, (or just give some laughs) :-d

[mariogus]

Personally ... What is the best way to organize the exceptions ... I have a huge code and with several exceptions that I do not know if they work ... any tip?

[JLM]

If the situation is a batch execution, being a procedure depends on the other, I think you better embed everything in a package, where you can develop several procedures.

[gpilger]

Will see the max was included later and the person did not lend themselves to remove the exception .... but that is funny is ... haha