Critical
Maintainability, Testability
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 (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | 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;
/
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | 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;
/
|