G-5030: Never assign predefined exception names to user defined exceptions.
Blocker
Reliability, Testability
Reason
This is error-prone because your local declaration overrides the global declaration. While it is technically possible to use the same names, it causes confusion for others needing to read and maintain this code. Additionally, you will need to be very careful to use the prefix standard in front of any reference that needs to use the default exception behavior of the Oracle Database.
Example (bad)
Using the code below, we are not able to handle the no_data_found exception raised by the select statement as we have overwritten that exception handler. In addition, our exception handler doesn't have an exception number assigned, which should be raised when the select statement does not find any rows.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 | declare
l_dummy dual.dummy%type;
no_data_found exception;
co_rownum constant simple_integer := 0;
co_no_data_found constant types_up.short_text_type := 'no_data_found';
begin
select dummy
into l_dummy
from dual
where rownum = co_rownum;
if l_dummy is null then
raise no_data_found;
end if;
exception
when no_data_found then
sys.dbms_output.put_line(co_no_data_found);
end;
/
|
| 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.
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | declare
l_dummy dual.dummy%type;
empty_value exception;
co_rownum constant simple_integer := 0;
co_empty_value constant types_up.short_text_type := 'empty_value';
co_no_data_found constant types_up.short_text_type := 'no_data_found';
begin
select dummy
into l_dummy
from dual
where rownum = co_rownum;
if l_dummy is null then
raise empty_value;
end if;
exception
when empty_value then
sys.dbms_output.put_line(co_empty_value);
when no_data_found then
sys.dbms_output.put_line(co_no_data_found);
end;
/
|