Skip to content

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; -- violates also naming convention G-9113
   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; -- NOSONAR: consequential error, violates G-5070
   end if;
exception
   when no_data_found then
      sys.dbms_output.put_line(co_no_data_found);
end;
/

1
2
3
4
5
6
Error report -
ORA-01403: no data found
ORA-06512: at line 7
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;
   e_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 e_empty_value;
   end if;
exception
   when e_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;
/