Chronological Tracing/Debugging with minimal intrusion in Oracle

Here is a simple way to provide chronological tracing, debugging or auditing of Oracle PL/SQL code, by inserting a minimally intrusive, no affect to session concurrency, non erroring code. You put calls to the procedure in your code and output will be written to file /usr/tmp/SID_debug.log if the code below is used exactly as is, where SID is your instance name. It works on Oracle 9i or higher, including 10g and Oracle XE database on Unix/Linux - change the directory as required for Windows, this directory must be included in database parameter utl_file_dir and the database needs to have been restarted if utl_file_dir is changed. To get the value utl_file_dir run the query:

select value from v$parameter where name = 'utl_file_dir';

You can call the procedure like this

mods_log('Hello, world!'); 

in PL/SQL. e.g. for database trigger or PL/SQL code

begin mods_log('Hello, world!'); end;

e.g. for SQL*Plus

exec mods_log('Hello, world!');

Here is the code to create the procedure:

create or replace procedure mods_log (pText varchar2) is
  pragma autonomous_transaction;
  vFP       utl_file.file_type;
  vFileName varchar2(20);
  vTime     varchar2(11);
begin
  select lower(instance_name) || '_debug.log'
  ,      to_char(sysdate,'MMDDHH24MISS ')
  into   vFileName
  ,      vTime
  from   v$instance;
  vFP := utl_file.fopen('/usr/tmp',vFileName,'a',6000);
  utl_file.put(vFP,vTime || pText);
  utl_file.fclose(vFP);
exception
when others then
  null; -- Do not report any errors for this procedure call
end;
/