G-5030
🆓Error
Never assign predefined exception names to user defined exceptions.
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
Non-Compliant Example
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;
/Issues
| Line | Column | Message |
|---|---|---|
| 3 | 4 | Never assign predefined exception names to user defined exceptions. |
Explanation
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.
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. Compliant Solution - ★★★★★
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;
/Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| PredefinedExceptionNames | Comma separated List of predefined exception names. | access_into_null, case_not_found,collection_is_null, cursor_already_open, dup_val_on_index, invalid_cursor, invalid_number, login_denied, no_data_found, no_data_needed, not_logged_on, program_error, rowtype_mismatch, self_is_null, storage_error, subscript_beyond_count, subscript_outside_limit, sys_invalid_rowid, timeout_on_resource, too_many_rows, value_error, zero_divide |
References
- same as Trivadis G-5030