Skip to content

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