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

Tests

Test SQL query

select username as identifier,
       'Schema ' || username || ' does not contain ''dbms_application_info.set_%'' calls.''' as message
  from dba_users u
 where username in (#SchemaNames#) -- config parameter #SchemaNames#
   and not exists (
          select 1
            from dba_source s
           where s.owner = u.username
             and regexp_like(s.text, 'dbms_application_info\s*\.\s*set_', 'i')
             and lower(s.text) not like '%--%dbms_application_info%'
       )
 order by username

References

same as Trivadis G-8510