During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


The first time I hit this kind of code I was a little bit confused … it never came to my mind to do something like redefining named exceptions like NO_DATA_FOUND or TOO_MANY_ROWS or even OTHERS.

But after many years I became pretty hardened when it comes to what to expect during a code review.

My own NO_DATA_FOUND Exception

So guess what this peace of code does…

declare
   L_Variable number;
   no_data_found EXCEPTION;
begin
   select empno
     into l_variable
     from scott.emp
    where ename = 'TYRION LENNISTER';
exception
    when no_data_found
       then sys.dbms_output.put_line('Tyrion does not exist');
end;

It ends with raising a no_data_found exception upon the caller…

Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Hold a minute…why does it raise an exception? There is an exception handler that should take care of no_data_found …
well yes … but …
That is a user defined exception which is different from the named exception that was automatically raised by Oracle. It shares the same name but it is different.

Now how about this?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      no_data_found EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
    exception
       when no_data_found
          then sys.dbms_output.put_line('Tyrion does not exist');
    end inner;
exception
   when no_data_found
      then sys.dbms_output.put_line('Yes, Tyrion really does not exist');
end outer;

This time we get the message

Yes, Tyrion really does not exist

because in the outer block the no_data_found exception declared in the inner block is not known and therefore we fall back to Oracles named exception NO_DATA_FOUND which is handled in the outer block.


My own OTHERS

How about an user defined others exception?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

I put the others handler ahead of the no_data_found just to make sure it is “my” others (you cannot do that with the named others) and …

Error report -
ORA-06550: line 14, column 7:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

So obviously that others was not mine but the one of oracle. Next try:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

This time I got no error

Hello, what did you expect

So I passed the others exception handler (my own) and fell into the no_data_found handler. The same happens when you write your code like this:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when others
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

But, honestly …

This is really confusing and in my eyes it is absolutely needless to confuse our colleagues by redefining named exceptions. So get rid of that!


Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.