G-8510: Always use dbms_application_info to track program process transiently.
Critical
Efficiency, Reliability
Unsupported in db* CODECOP Validators
We cannot know where the use of dbms_application_info is sensible. Algorithms to detect wrong, missing and right usages of this pattern are virtually impossible to implement without understanding the context.
Reason
This technique allows us to view progress of a process without having to persistently write log data in either a table or a file. The information is accessible through the v$session view.
Example (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | create or replace package body employee_api is
procedure process_emps is
begin
<<employees>>
for emp_rec in (
select employee_id
from employees
order by employee_id
)
loop
-- some processing
sys.dbms_output.put_line(emp_rec.employee_id);
end loop employees;
end process_emps;
end employee_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 | create or replace package body employee_api is
procedure process_emps is
co_action_name constant v$session.action%type := 'init';
co_label constant v$session.action%type := 'Processing ';
begin
sys.dbms_application_info.set_module(
module_name => $$plsql_unit
,action_name => co_action_name
);
<<employees>>
for emp_rec in (
select employee_id
from employees
order by employee_id
)
loop
-- some processing
sys.dbms_application_info.set_action(co_label || emp_rec.employee_id);
end loop employees;
end process_emps;
end employee_api;
/
|