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 Oracle’s default exception behavior.

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.

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)

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;
/