G-8510: Always use dbms_application_info to track program process transiently.
Minor
Efficiency, Reliability
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 | create or replace package body employee_api is
procedure process_emps is
begin
<<employees>>
for emp_rec in (select employee_id
from employee
order by employee_id)
loop
null; -- some processing
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 | create or replace package body employee_api is
procedure process_emps is
begin
sys.dbms_application_info.set_module(module_name => $$plsql_unit
,action_name => 'init');
<<employees>>
for emp_rec in (select employee_id
from employee
order by employee_id)
loop
sys.dbms_application_info.set_action('processing ' || emp_rec.employee_id);
end loop employees;
end process_emps;
end employee_api;
/
|