G-5050

🆓

Warning

Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded 20nnn error number or hard-coded message.

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

Reason

If you are not very organized in the way you allocate, define and use the error numbers between 20999 and 20000 (those reserved by the Oracle Database for its user community), it is very easy to end up with conflicting usages. You should assign these error numbers to named constants and consolidate all definitions within a single package. When you call raise_application_error, you should reference these named elements and error message text stored in a table. Use your own raise procedure in place of explicit calls to raise_application_error. If you are raising a "system" exception like no_data_found, you must use raise. However, when you want to raise an application-specific error, you use raise_application_error. If you use the latter, you then have to provide an error number and message. This leads to unnecessary and damaging hard-coded values. A more fail-safe approach is to provide a predefined raise procedure that automatically checks the error number and determines the correct way to raise the error.

Example

Non-Compliant Example

declare
   co_invalid_emp_text constant types_up.text := 'Invalid employee_id';
begin
   raise_application_error(-20501,co_invalid_emp_text);
end;
/

Issues

Line	Column	Message
4	28	Using RAISE_APPLICATION_ERROR with a hard-coded 20nnn error number.

Compliant Solution - ★★★★★

begin
   err_up.raise(in_error => err.co_invalid_employee_id);
end;
/

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
FirstUserDefinedErrorCode	The first user-defined error code that can be used in raise_application_error.	-20000
LastUserDefinedErrorCode	The last user-defined error code that can be used in raise_application_error.	-20999

References

same as Trivadis G-5050