G-5080

🆓

Warning

Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.

Rule Details

Severity Level: Critical
Quality Area: Maintainability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v7.3.4 +)

Reason

In exception handler sqlerrm and format_error_stack won't tell you the exact line where the error occurred. format_error_backtrace displays the call stack at the point where an exception was raised, even if the subprogram is called from an exception handler in an outer scope.

If you use sqlerrm or format_error_stack to log/display error, you should also include format_error_backtrace to identify the exact location where the exception was raised.

Example

Non-Compliant Example

create or replace package body order_api as
   procedure discount_and_recalculate(
      in_customer_id in integer
     ,in_discount    in number
   ) is
      co_customer_id constant customer.id%type                  := in_customer_id;
      co_discount    constant customer.discount_percentage%type := in_discount;
      co_error_label constant type_up.text                      := 'Error: ';
   begin
      customer_api.apply_discount(
         in_customer_id => co_customer_id
        ,in_discount    => co_discount
      );
      customer_api.calc(co_customer_id);
   exception
      when zero_divide then
         null; -- ignore
      when others then
         logging_package.log_error(co_error_label || sqlerrm);
         raise;
   end discount_and_recalculate;
end order_api;
/

Issues

Line	Column	Message
19	54	Missing FORMAT_ERROR_BACKTRACE call.

Compliant Solution - ★★★★★

create or replace package body order_api as
   procedure discount_and_recalculate(
      in_customer_id in integer
     ,in_discount    in number
   ) is
      co_customer_id     constant customer.id%type                  := in_customer_id;
      co_discount        constant customer.discount_percentage%type := in_discount;
      co_error_label     constant type_up.text                      := 'Error: ';
      co_backtrace_label constant type_up.text                      := ' - Backtrace: ';
   begin
      customer_api.apply_discount(
         in_customer_id => co_customer_id
        ,in_discount    => co_discount
      );
      customer_api.calc(co_customer_id);
   exception
      when zero_divide then
         null; -- ignore
      when others then
         logging_package.log_error(
            co_error_label
            || sqlerrm
            || co_backtrace_label
            || sys.dbms_utility.format_error_backtrace
         );
         raise;
   end discount_and_recalculate;
end order_api;
/

References

same as Trivadis G-5080
similar to plsql:FormatErrorStackAndBacktraceUsedAlongside
The scope of plsql:FormatErrorStackAndBacktraceUsedAlongside is limited to FORMAT_ERROR_BACKTRACE and FORMAT_ERROR_STACK and ignores SQLERRM.