G-8510

🆓

Warning

Always use dbms_application_info to track program process transiently.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

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

Issues

Line	Column	Message
12	10	Always use dbms_application_info to track program process transiently.

Compliant Solution - ★★★★★

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

References

same as Trivadis G-8510