Skip to content

G-8510: Always use dbms_application_info to track program process transiently.

Minor

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
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
         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
16
17
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 employees
          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;
/