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;
/