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)

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)

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