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.
