Redundant Sysdate

[NiNo]

Select all

Declare 
  vData Date; 
Begin 
  Select Sysdate Into vData From Dual; 
Exception 
  When Others Then 
    vDate := Sysdate; 
End;

[mportes]

Arrrghh! When Others ... http://mportes.blogspot.com/2005/07/boa ... thers.html

[Trevisolli]

Márcio, good morning.

could be posting the code here in the forum ???

[dr_gori]

It's a shame, I also could not see ...
I love Márcio Portes's blog ... but because it's a blog, the site is blocked in many companies I'vê worked on. (block the String Blog) ...
But it's certain that I'll look at home :-

[Toad]

A colleague had a question on a mailing list and motivated me to write on the subject here, since I believe it is of common interest. These are improper and indiscriminate use of the WHEN OTHERS clause in PL / SQL codes.

As in any programming language, in PL / SQL we can also treat exceptions. The problem is that often for "lack of time" or lazy even, many encoders (DBAs, analysts, etc.) simply so that their codes do not present error, play the Exception When other then null clause b] To prevent the program from being aborted. This in 99% of the cases is a bug. The clause masks any exception in the program and makes it very difficult to debug the code.

Let's go to a hypothetical example (because this never happens in real life) missing 5 days for the closing of the month, the manager decides to meet an awards that the board authorized and asks developers to codify A function to increase employee salary by 10%. As the developer is a sagacious analyst, he can not let his code abortion, then he will treat all exceptions through the When Ohers. See the loss of employees.

Select all

 
ops$marcio@ORA10G> create table func as select empno, ename, sal from scott.emp; 
 
Table created. 
 
ops$marcio@ORA10G> 
ops$marcio@ORA10G> create or replace function 
  2  aumenta_sal( sal in number ) return number 
  3  is 
  4     l_novo_sal  number default 0; 
  5  begin 
  6     l_novo_sal := sal / 0; -- ops! certamente um erro! 
  7  end; 
  8  / 
 
Function created. 
 
ops$marcio@ORA10G> show error 
No errors. 

No errors! However, when the developer who has implemented the wheel_folha begins to test his program, he does not want to know about problems, so he lags the WHEN OTHERS and NULL clause to really does not have surprises.

Select all

 
ops$marcio@ORA10G> 
ops$marcio@ORA10G> create or replace procedure 
  2  roda_folha 
  3  is 
  4  begin 
  5    update func 
  6       set sal = aumenta_sal(sal); 
  7  exception 
  8    when others then 
  9       null; 
 10  end; 
 11  / 
 
Procedure created. 
 
ops$marcio@ORA10G> show error 
No errors. 

and our hopeful heroes with the increase!

Select all

 
ops$marcio@ORA10G> select * from func; 
 
        EMPNO ENAME                SAL 
------------- ---------- ------------- 
         7369 SMITH                800 
         7499 ALLEN               1600 
         7521 WARD                1250 
         7566 JONES               2975 
         7654 MARTIN              1250 
         7698 BLAKE               2850 
         7782 CLARK               2450 
         7788 SCOTT               3000 
         7839 KING                5000 
         7844 TURNER              1500 
         7876 ADAMS               1100 
         7900 JAMES                950 
         7902 FORD                3000 
         7934 MILLER              1300 
 
14 rows selected. 
 
ops$marcio@ORA10G> exec roda_folha 
 
PL/SQL procedure successfully completed. 

No problem when we rotate the sheet. Happy operators did not hit anyone that night!
But the other day!

Select all

 
ops$marcio@ORA10G> 
ops$marcio@ORA10G> select * from func; 
 
        EMPNO ENAME                SAL 
------------- ---------- ------------- 
         7369 SMITH                800 
         7499 ALLEN               1600 
         7521 WARD                1250 
         7566 JONES               2975 
         7654 MARTIN              1250 
         7698 BLAKE               2850 
         7782 CLARK               2450 
         7788 SCOTT               3000 
         7839 KING                5000 
         7844 TURNER              1500 
         7876 ADAMS               1100 
         7900 JAMES                950 
         7902 FORD                3000 
         7934 MILLER              1300 
 
14 rows selected. 

What should be:

Select all

 
ops$marcio@ORA10G> 
ops$marcio@ORA10G> select func.*, sal * 1.10 novo_sal 
  2    from func; 
 
        EMPNO ENAME                SAL      NOVO_SAL 
------------- ---------- ------------- ------------- 
         7369 SMITH                800           880 
         7499 ALLEN               1600          1760 
         7521 WARD                1250          1375 
         7566 JONES               2975        3272,5 
         7654 MARTIN              1250          1375 
         7698 BLAKE               2850          3135 
         7782 CLARK               2450          2695 
         7788 SCOTT               3000          3300 
         7839 KING                5000          5500 
         7844 TURNER              1500          1650 
         7876 ADAMS               1100          1210 
         7900 JAMES                950          1045 
         7902 FORD                3000          3300 
         7934 MILLER              1300          1430 
 
14 rows selected. 

This small example He tried to illustrate what happens to the bad practice of programming when using the factifice of the When Others clause.

The colleague's doubt is if there is any way to block the use of the When Others clause in Triggers source code. We recommend it to prevent all codes!
If it is to be drastic, I will kill evil by the root. The method used was: I created a DDL (CREATE and ALTER) event trigger, I checked if the Create or Alter was for trigger and searched the Source code the When Ohers; Found, I return a user error with a message "Undesirable use of the ithers"!
Demonstrating:

Select all

 
ops$marcio@ORA10G> create or replace trigger prevent_when 
  2     after create or alter on schema 
  3  declare 
  4     sql_text ora_name_list_t; 
  5     stmt     varchar2(2000); 
  6     n        number; 
  7  begin 
  8     if ( ora_dict_obj_type = 'TRIGGER' ) then 
  9        n := ora_sql_txt(sql_text); 
 10        for i in 1 .. n 
 11        loop 
 12            stmt := stmt || sql_text(i); 
 13        end loop; 
 14        if ( instr(upper(stmt), 'WHEN OTHERS') > 0 ) then 
 15           raise_application_error( -20001, 'Uso indesejavel do WHEN OTHERS'); 
 16        end if; 
 17     end if; 
 18  end; 
 19  / 
 
Trigger created. 
 
ops$marcio@ORA10G> 
ops$marcio@ORA10G> create table t ( x int ); 
 
Table created. 
 
ops$marcio@ORA10G> create or replace trigger t_bi_fer 
  2  before insert on t for each row 
  3  declare 
  4     n number; 
  5  begin 
  6     n := 10/0; 
  7  exception 
  8    when others then 
  9       null; 
 10  end; 
 11  / 
create or replace trigger t_bi_fer 
                          * 
ERROR at line 1: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-20001: Uso indesejavel do WHEN OTHERS 
ORA-06512: at line 13 

Obviously, the above example must be perfected, since it is possible to separate when others into two lines or put more spaces between them. The demonstration is only for an idea of ??how to implement the block.

In the whole text, clearly note the excessive use of the When Others clause. This was purposeful, to stress. So when they come in some code, remember what was discussed here.

Withdrawn: http://mportes.blogspot.com/2005/07/boa ... thers.html
: -O